﻿using AutoMapper;
using Dapper;
using DocumentFormat.OpenXml.Bibliography;
using DocumentFormat.OpenXml.Drawing;
using DocumentFormat.OpenXml.Drawing.Charts;
using DocumentFormat.OpenXml.Math;
using DocumentFormat.OpenXml.Wordprocessing;
using Hangfire;
using Hangfire.LiteDB;
using housingsafety.server.Caching.Core;
using housingsafety.server.Entities;
using housingsafety.server.Entities.DataModels;
using housingsafety.server.Helpers;
using housingsafety.server.Models;
using housingsafety.server.Repository;
using housingsafety.server.Repository.Extensions;
using housingsafety.server.Svcs;
using KS3;
using KS3.Model;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using Minio.DataModel;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Npgsql;
using NPOI.HPSF;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Org.BouncyCastle.Asn1.Ocsp;
using Quartz.Util;
using RestSharp.Extensions;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using static housingsafety.server.Controllers.HouseMngCjCountController;
using static housingsafety.server.Controllers.HouseMngCountController;

namespace housingsafety.server.Controllers
{
    /// <summary>
    /// 房屋管理服务
    /// </summary>
    public class HouseMngSqjzCountController : BaseController<HouseMngController>
    {
        public IConfiguration Configuration { get; }
        private readonly ICache _cache;
        private readonly IMapper _mapper;
        private readonly IStorageProvider _storageProvider;
        private readonly ZmDbContext _dbContext;
        private readonly IRepository<ZmUser> _userRepository;
        private readonly IRepository<ZmBuildings> _buildingRepository;
        private readonly IRepository<ZmFileAttach> _fileAttachRepository;
        private readonly IOptions<StaticDataConf> _staticVtOptions;
        private readonly IRepository<ZmDepartment> _departmentRepository;
        private readonly IRepository<ZmPcYhLogDx> _zmPcYhLogDxRepository;
        private readonly IRepository<ZmPcYhLog> _zmPcYhLogRepository;
        private readonly IRepository<ZmPcYh> _zmPcYhRepository;
        private readonly IRepository<ZmPcBase> _zmPcBaseRepository;
        private readonly IRepository<ZmPcBaseLog> _zmPcBaseLogRepository;

        private readonly IDbTransaction _transaction;

        private readonly Microsoft.AspNetCore.Http.Features.FormOptions _defaultFormOptions = new Microsoft.AspNetCore.Http.Features.FormOptions();
        private readonly string[] _permittedExtensions = { ".mp3", ".mp4", ".jpg", ".jpeg", ".png", ".geojson", ".doc", ".docx" };

        private static object _lock = new object();

        private readonly KS3Client _kS3Client;


        public HouseMngSqjzCountController(IStorageProvider storageProvider
            , ZmDbContext dbContext
            , ILogger<HouseMngController> logger,
            IConfiguration configuration
            , IRepository<ZmSystemLog> systemLogRepository) : base(logger, systemLogRepository)
        {
            _dbContext = dbContext;
            _storageProvider = storageProvider;
            Configuration = configuration;
        }

        public class Param
        {
            public string id { get; set; }

            public string msgid { get; set; }

            public List<DateTime> Time { get; set; }

            public string villagedm { get; set; }
        }
        private List<string> JsonStr2List(string jsonStr)
        {
            try
            {
                return JsonConvert.DeserializeObject<List<string>>(jsonStr);
            }
            catch (System.Exception)
            {
            }
            return null;
        }
        private object JsonRegion(string jsonStr)
        {
            try
            {
                return JsonConvert.DeserializeObject<object>(jsonStr);
            }
            catch (System.Exception)
            {
            }

            return null;
        }


        /// <summary>
        /// 村级检查统计-人员疏散通道
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("housescjjc/rysstd")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> Rysstd([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
            var a = JsonRegion(user.District);
            JObject obj = JObject.Parse(user.District);
            string RegionCode = (string)obj["RegionCode"];
            RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0, RegionCode.Length - 3) : RegionCode;
            if (string.IsNullOrWhiteSpace(param.code))
            {
                param.code = RegionCode;
            }
            param.code = param.code.Substring(param.code.Length - 3) == "000" ? param.code.Substring(0, param.code.Length - 3) : param.code;
            if (param.date == null || param.date.Count != 2)
            {
                return new RespRet<object>(false, "时间不能为空");
            }

            if (param.date[1] > DateTime.Now)
            {
                param.date[1] = DateTime.Today;
            }
            param.date[1] = param.date[1].AddHours(23);
            param.date[1] = param.date[1].AddMinutes(59);
            param.date[1] = param.date[1].AddSeconds(59);
            string sql = @" ";
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string starttime = Convert.ToDateTime(param.date[0] + "").ToString("yyyy-MM-dd");
            string endtime = Convert.ToDateTime(param.date[1] + "").ToString("yyyy-MM-dd");
            string tablename = "";
            string filed = "";
            string fileWhere = "";
            string table_name = "zm_building_fwzt_" + param.date[1].Year;
            if (endtime + "" == today)
            {
                tablename = "zm_pc_base_cj";
            }
            else
            {
                string startimemonth = Convert.ToDateTime(param.date[0] + "").ToString("yyyy_MM");
                string endmonth = Convert.ToDateTime(param.date[1] + "").ToString("yyyy_MM");
                string month = "";
                if (startimemonth == endmonth)
                {
                    month = startimemonth;
                    //tablename = "zm_pc_base_cj_" + month;
                    tablename = "zm_pc_baselog_cj";
                    filed = "ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,";
                    fileWhere = " and row_num=1 ";
                }
                else
                {
                    tablename = "zm_pc_baselog_cj";
                    filed = "ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,";
                    fileWhere = " and row_num=1 ";
                }
            }

            if (param.code.Length == 3 || param.code.Length == 6)
            {
                sql = " select * from  rysstd_gq_jc_text('" + filed + "','" + fileWhere +"','" + starttime + " 00:00:00','" + endtime + " 23:59:59','" + tablename + "','"+table_name+"') ";
            }

            if (param.code.Length == 9)
            {
                sql = " select * from  rysstd_xz_jc_text('" + filed + "','" + fileWhere + "','" + starttime + " 00:00:00','" + endtime + " 23:59:59','" + tablename + "','%" + param.code + "%','"+table_name+"') ";
            }
            if (param.code.Length == 12)
            {
                sql = " select * from  rysstd_cj_jc_text('" + filed + "','" + fileWhere + "','" + starttime + " 00:00:00','" + endtime + " 23:59:59','" + tablename + "','%" + param.code + "%','"+table_name+"') ";
            }
            var msg = _dbContext.Database.GetDbConnection().Query<RysstdDto>(sql, new { @code = "%" + param.code + "%", @startime = param.date[0], @endtime = param.date[1] }).FirstOrDefault();
            var tb = new List<object>();
            tb.Add(new { value = msg.dbl, name = "已达标" });
            tb.Add(new { value = 100 - msg.dbl, name = "未达标" });
            return new RespRet<object>(true, "Ok", new { msg = msg, tb = tb });
        }


        /// <summary>
        /// 街镇核查统计-人员疏散通道
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("housesjzhc/rysstd")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> JzhcRysstd([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
            var a = JsonRegion(user.District);
            JObject obj = JObject.Parse(user.District);
            string RegionCode = (string)obj["RegionCode"];
            RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0, RegionCode.Length - 3) : RegionCode;
            if (string.IsNullOrWhiteSpace(param.code))
            {
                param.code = RegionCode;
            }
            param.code = param.code.Substring(param.code.Length - 3) == "000" ? param.code.Substring(0, param.code.Length - 3) : param.code;
            if (param.date == null || param.date.Count != 2)
            {
                return new RespRet<object>(false, "时间不能为空");
            }

            if (param.date[1] > DateTime.Now)
            {
                param.date[1] = DateTime.Today;
            }
            param.date[1] = param.date[1].AddHours(23);
            param.date[1] = param.date[1].AddMinutes(59);
            param.date[1] = param.date[1].AddSeconds(59);
            string sql = @" ";
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string starttime = Convert.ToDateTime(param.date[0] + "").ToString("yyyy-MM-dd");
            string endtime = Convert.ToDateTime(param.date[1] + "").ToString("yyyy-MM-dd");
            string tablename = "";
            string filed = "";
            string fileWhere = "";
            string table_name = "zm_building_fwzt_" + param.date[1].Year;
            if (endtime + "" == today)
            {
                tablename = "zm_pc_base_sqjz";
            }
            else
            {
                string startimemonth = Convert.ToDateTime(param.date[0] + "").ToString("yyyy_MM");
                string endmonth = Convert.ToDateTime(param.date[1] + "").ToString("yyyy_MM");
                string month = "";
                if (startimemonth == endmonth)
                {
                    month = startimemonth;
                    //tablename = "zm_pc_baselog_sqjz" + month;
                    tablename = "zm_pc_baselog_sqjz";
                    filed = "ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,";
                    fileWhere = " and row_num=1 ";
                }
                else 
                {
                    tablename = "zm_pc_baselog_sqjz";
                    filed = "ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,";
                    fileWhere = " and row_num=1 ";
                }
            }

            if (param.code.Length == 3 || param.code.Length == 6)
            {
                sql = " select * from  rysstd_gq_hc_text('" + filed +"','" + fileWhere +"','" + starttime + " 00:00:00','" + endtime + " 23:59:59','" + tablename + "','4','"+table_name+"') ";
            }

            if (param.code.Length == 9)
            {
                sql = " select * from  rysstd_xz_hc_text('" + filed + "','" + fileWhere + "','" + starttime + " 00:00:00','" + endtime + " 23:59:59','" + tablename + "','%" + param.code + "%','4','"+table_name+"') ";
            }
            if (param.code.Length == 12)
            {
                sql = " select * from  rysstd_cj_hc_text('" + filed + "','" + fileWhere + "','" + starttime + " 00:00:00','" + endtime + " 23:59:59','" + tablename + "','%" + param.code + "%','4','"+table_name+"') ";
            }
            var msg = _dbContext.Database.GetDbConnection().Query<RysstdDto>(sql, new { @code = "%" + param.code + "%", @startime = param.date[0], @endtime = param.date[1] }).FirstOrDefault();
            var tb = new List<object>();
            tb.Add(new { value = msg.dbl, name = "已达标" });
            tb.Add(new { value = 100 - msg.dbl, name = "未达标" });
            return new RespRet<object>(true, "Ok", new { msg = msg, tb = tb });
        }





        /// <summary>
        /// 区级核查统计-人员疏散通道
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("housesqjhc/rysstd")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> QjhcRysstd([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
            var a = JsonRegion(user.District);
            JObject obj = JObject.Parse(user.District);
            string RegionCode = (string)obj["RegionCode"];
            RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0, RegionCode.Length - 3) : RegionCode;
            if (string.IsNullOrWhiteSpace(param.code))
            {
                param.code = RegionCode;
            }
            param.code = param.code.Substring(param.code.Length - 3) == "000" ? param.code.Substring(0, param.code.Length - 3) : param.code;
            if (param.date == null || param.date.Count != 2)
            {
                return new RespRet<object>(false, "时间不能为空");
            }

            if (param.date[1] > DateTime.Now)
            {
                param.date[1] = DateTime.Today;
            }
            param.date[1] = param.date[1].AddHours(23);
            param.date[1] = param.date[1].AddMinutes(59);
            param.date[1] = param.date[1].AddSeconds(59);
            string sql = @" ";
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string starttime = Convert.ToDateTime(param.date[0] + "").ToString("yyyy-MM-dd");
            string endtime = Convert.ToDateTime(param.date[1] + "").ToString("yyyy-MM-dd");
            string tablename = "";
            string filed = "";
            string fileWhere = "";
            string table_name = "zm_building_fwzt_" + param.date[1].Year;
            if (endtime + "" == today)
            {
                tablename = "zm_pc_base_sqjz";
            }
            else
            {
                string startimemonth = Convert.ToDateTime(param.date[0] + "").ToString("yyyy_MM");
                string endmonth = Convert.ToDateTime(param.date[1] + "").ToString("yyyy_MM");
                string month = "";
                if (startimemonth == endmonth)
                {
                    month = startimemonth;
                    //tablename = "zm_pc_baselog_sqjz" + month;
                    tablename = "zm_pc_baselog_sqjz";
                    filed = "ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,";
                    fileWhere = " and row_num=1 ";
                }
                else
                {
                    tablename = "zm_pc_baselog_sqjz";
                    filed = "ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,";
                    fileWhere = " and row_num=1 ";
                }
            }

            if (param.code.Length == 3 || param.code.Length == 6)
            {
                sql = " select * from  rysstd_gq_hc_text('" + filed + "','" + fileWhere + "','" + starttime + " 00:00:00','" + endtime + " 23:59:59','" + tablename + "','3','" + table_name + "') ";
            }

            if (param.code.Length == 9)
            {
                sql = " select * from  rysstd_xz_hc_text('" + filed + "','" + fileWhere + "','" + starttime + " 00:00:00','" + endtime + " 23:59:59','" + tablename + "','%" + param.code + "%','3','" + table_name + "') ";
            }
            if (param.code.Length == 12)
            {
                sql = " select * from  rysstd_cj_hc_text('" + filed + "','" + fileWhere + "','" + starttime + " 00:00:00','" + endtime + " 23:59:59','" + tablename + "','%" + param.code + "%','3','" + table_name + "') ";
            }
            var msg = _dbContext.Database.GetDbConnection().Query<RysstdDto>(sql, new { }).FirstOrDefault();
            var tb = new List<object>();
            tb.Add(new { value = msg.dbl, name = "已达标" });
            tb.Add(new { value = 100 - msg.dbl, name = "未达标" });
            return new RespRet<object>(true, "Ok", new { msg = msg, tb = tb });
        }


        /// <summary>
        /// 市级核查统计-人员疏散通道
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("housessjhc/rysstd")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> SjhcRysstd([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
            var a = JsonRegion(user.District);
            JObject obj = JObject.Parse(user.District);
            string RegionCode = (string)obj["RegionCode"];
            RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0, RegionCode.Length - 3) : RegionCode;
            if (string.IsNullOrWhiteSpace(param.code))
            {
                param.code = RegionCode;
            }
            param.code = param.code.Substring(param.code.Length - 3) == "000" ? param.code.Substring(0, param.code.Length - 3) : param.code;
            if (param.date == null || param.date.Count != 2)
            {
                return new RespRet<object>(false, "时间不能为空");
            }

            if (param.date[1] > DateTime.Now)
            {
                param.date[1] = DateTime.Today;
            }
            param.date[1] = param.date[1].AddHours(23);
            param.date[1] = param.date[1].AddMinutes(59);
            param.date[1] = param.date[1].AddSeconds(59);
            string sql = @" ";
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string starttime = Convert.ToDateTime(param.date[0] + "").ToString("yyyy-MM-dd");
            string endtime = Convert.ToDateTime(param.date[1] + "").ToString("yyyy-MM-dd");
            string tablename = "";
            string filed = "";
            string fileWhere = "";
            string table_name = "zm_building_fwzt_" + param.date[1].Year;
            if (endtime + "" == today)
            {
                tablename = "zm_pc_base_sqjz";
            }
            else
            {
                string startimemonth = Convert.ToDateTime(param.date[0] + "").ToString("yyyy_MM");
                string endmonth = Convert.ToDateTime(param.date[1] + "").ToString("yyyy_MM");
                string month = "";
                if (startimemonth == endmonth)
                {
                    month = startimemonth;
                    //tablename = "zm_pc_baselog_sqjz" + month;
                    tablename = "zm_pc_baselog_sqjz";
                    filed = "ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,";
                    fileWhere = " and row_num=1 ";
                }
                else
                {
                    tablename = "zm_pc_baselog_sqjz";
                    filed = "ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,";
                    fileWhere = " and row_num=1 ";
                }
            }
            if (param.code.Length == 3 || param.code.Length == 6)
            {
                sql = " select * from  rysstd_gq_hc_text('" + filed + "','" + fileWhere + "','" + starttime + " 00:00:00','" + endtime + " 23:59:59','" + tablename + "','2','" + table_name + "') ";
            }

            if (param.code.Length == 9)
            {
                sql = " select * from  rysstd_xz_hc_text('" + filed + "','" + fileWhere + "','" + starttime + " 00:00:00','" + endtime + " 23:59:59','" + tablename + "','%" + param.code + "%','2','" + table_name + "') ";
            }
            if (param.code.Length == 12)
            {
                sql = " select * from  rysstd_cj_hc_text('" + filed + "','" + fileWhere + "','" + starttime + " 00:00:00','" + endtime + " 23:59:59','" + tablename + "','%" + param.code + "%','2','" + table_name + "') ";
            }
            var msg = _dbContext.Database.GetDbConnection().Query<RysstdDto>(sql, new { @code = "%" + param.code + "%", @startime = param.date[0], @endtime = param.date[1] }).FirstOrDefault();
            var tb = new List<object>();
            tb.Add(new { value = msg.dbl, name = "已达标" });
            tb.Add(new { value = 100 - msg.dbl, name = "未达标" });
            return new RespRet<object>(true, "Ok", new { msg = msg, tb = tb });
        }





        /// <summary>
        /// 达标率排名村级检查
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("housescjjc/dblbar")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<object> Dblbar([FromBody] DateQuery param)
        {
            try
            {
                var usernNumber = this.CurrentUser();
                var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
                var a = JsonRegion(user.District);
                JObject obj = JObject.Parse(user.District);
                string RegionCode = (string)obj["RegionCode"];
                RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0, RegionCode.Length - 3) : RegionCode;
                var isxz = true;
                if (param.date == null || param.date.Count != 2)
                {
                    return new RespRet<object>(false, "时间不能为空");
                }

                if (param.date[1] > DateTime.Now)
                {
                    param.date[1] = DateTime.Today;
                }
                param.date[1] = param.date[1].AddHours(23);
                param.date[1] = param.date[1].AddMinutes(59);
                param.date[1] = param.date[1].AddSeconds(59);
                string formattedDate = param.date[1].ToString("yyyy-MM-dd HH:mm:ss");
                string table_name = "zm_building_fwzt_" + param.date[1].Year;

                if (string.IsNullOrWhiteSpace(param.code))
                {
                    isxz = false;
                    param.code = RegionCode;
                }
                param.code = param.code.Substring(param.code.Length - 3) == "000" ? param.code.Substring(0, param.code.Length - 3) : param.code;

                string sql = @" ";
                if (param.code.Length == 3 || param.code.Length == 6)
                { 
                    sql = $@"  
select   zcount,(zcount-ydb) wdb,ydb,name,code,CASE WHEN dbl > 100 THEN 100.00  when dbl is null then 0.00 ELSE dbl END dbl  from  (select ydb,zcount,xzq name,xzqdm code,case when  round(ydb*1.00*100/case when zcount =0 then null else zcount end ,2) is null then 0.00 else round(ydb*1.00*100/case when zcount =0 then null else zcount end ,2) end   dbl from  (select max(jbxxgqview.xzq) xzq,jbxxgqview.xzqdm,sum(jbxxgqview.zcount) zcount,sum(ssltdbfjsl) ydb   from (SELECT 
    t1.xzq,
    t1.xzqdm,
    COALESCE((t1.zcount)::numeric, (0)::numeric) AS zcount,
		COALESCE(t2.ssltdbfjsl, (0)::bigint) AS ssltdbfjsl
FROM (
    (
       SELECT 
       count( case when fwbh in (select distinct fwbh from zm_pc_yhlog_cj where create_on between @startime  AND @endtime::TIMESTAMP ) and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)  then fwbh end) AS zcount,
      zm_buildings.xzq,
      zm_buildings.xzqdm 
  FROM zm_buildings   
  GROUP BY zm_buildings.xzqdm, zm_buildings.xzq
  ORDER BY zm_buildings.xzqdm
    ) AS t1
    LEFT JOIN (
       SELECT 
                count(1) ssltdbfjsl,xzqdm,xzq
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.create_on, t1.xzq,jsrysstd, t1.xzqdm, t1.fwbh
            FROM  zm_pc_baselog_cj t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE t1.create_on between @startime  AND @endtime::TIMESTAMP  and sfyzc='是'  
        ) AS zm_pc_base where row_num =1   and jsrysstd='是' and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)  GROUP BY xzqdm,xzq
    ) AS t2 ON t1.xzqdm::text = t2.xzqdm::text
) 
) jbxxgqview  GROUP BY xzqdm ) a ORDER BY dbl desc,xzqdm )a 	
	  ";
                }
                if (param.code.Length == 6 && isxz)
                {
                    sql = $@"  
  select  xzqdm,xzq,zcount,(zcount-ydb) wdb,ydb,name,code,CASE WHEN dbl > 100 THEN 100.00  when dbl is null then 0.00 ELSE dbl END dbl  from  (select COALESCE(ssltdbfjsl,0) ydb,zcount,xzqdm,xzq,town name,towndm code,case when  round(COALESCE(ssltdbfjsl,0)*1.00*100/case when zcount =0 then null else zcount end ,2) is null then 0.00 else round(COALESCE(ssltdbfjsl,0)*1.00*100/case when zcount =0 then null else zcount end ,2) end   dbl from  (SELECT 
    t1.xzq,
    t1.xzqdm,
		t1.town,
		t1.towndm,
    COALESCE((t1.zcount)::numeric, (0)::numeric) AS zcount,
    COALESCE(t2.ssltdbfjsl, (0)::bigint) AS ssltdbfjsl
FROM (
    (
              SELECT 
         count( case when fwbh in (select distinct fwbh from zm_pc_yhlog_cj where create_on between @startime  AND @endtime::TIMESTAMP ) and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)  then fwbh end) AS zcount,
     max(zm_buildings.xzq) xzq,
     max(zm_buildings.xzqdm) xzqdm,
     max(town) town ,towndm
  FROM zm_buildings   
  GROUP BY zm_buildings.towndm
  ORDER BY zm_buildings.towndm
    ) AS t1
    LEFT JOIN (
        SELECT 
                count(1) ssltdbfjsl,town
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.town,jsrysstd, t1.fwbh,fwzt 
            FROM  zm_pc_baselog_cj t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE t1.create_on BETWEEN @startime AND @endtime::TIMESTAMP and sfyzc='是'  
        ) AS zm_pc_base  where row_num =1   and jsrysstd='是' and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime) GROUP BY town
    ) AS t2 ON t1.towndm::text = t2.town::text
) 
 where t1.towndm like @code
) jbxxgqview   ) a ORDER BY dbl desc,code ";
                }
                if (param.code.Length == 9)
                {
                    sql = $@"select zcount,(zcount-ydb) wdb,town,towndm,xzqdm,xzq,zcount,ydb,name,code,CASE WHEN dbl > 100 THEN 100.00  when dbl is null then 0.00 ELSE dbl END dbl  from  (select COALESCE(ssltdbfjsl,0) ydb,zcount,xzqdm,xzq,village name,villagedm code,town,towndm,case when  round(COALESCE(ssltdbfjsl,0)*1.00*100/case when zcount =0 then null else zcount end ,2) is null then 0.00 else round(COALESCE(ssltdbfjsl,0)*1.00*100/case when zcount =0 then null else zcount end ,2) end   dbl from  (SELECT 
		t1.town,
		t1.towndm,
    t1.xzq,
    t1.xzqdm,
		t1.village,
		t1.villagedm,
    COALESCE((t1.zcount)::numeric, (0)::numeric) AS zcount,
    COALESCE(t2.ssltdbfjsl, (0)::bigint) AS ssltdbfjsl
FROM (
    (
       
              SELECT 
      count( case when fwbh in (select distinct fwbh from zm_pc_yhlog_cj where create_on between @startime  AND @endtime::TIMESTAMP ) and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)  then fwbh end) AS zcount,
     max(zm_buildings.xzq) xzq,
     max(zm_buildings.xzqdm) xzqdm,
     max(town) town ,max(towndm) towndm,villagedm,max(village) village
  FROM zm_buildings   
  GROUP BY zm_buildings.villagedm
  ORDER BY zm_buildings.villagedm
    ) AS t1
    LEFT JOIN (
         SELECT 
                count(1) ssltdbfjsl,village
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.village,jsrysstd, t1.fwbh,fwzt 
            FROM  zm_pc_baselog_cj t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE t1.create_on BETWEEN @startime AND @endtime::TIMESTAMP and sfyzc='是'  
        ) AS zm_pc_base where row_num =1   and jsrysstd='是' and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime) GROUP BY village
    ) AS t2 ON t1.villagedm::text = t2.village::text
) 
 where t1.villagedm like @code
) jbxxgqview   ) a ORDER BY dbl desc,code 
 ";
                }
                if (param.code.Length == 12)
                {
                    sql = $@" select zcount,(zcount-ydb) wdb,town,towndm,xzqdm,xzq,zcount,ydb,name,code,CASE WHEN dbl > 100 THEN 100.00  when dbl is null then 0.00 ELSE dbl END dbl  from  (select COALESCE(ssltdbfjsl,0) ydb,zcount,xzqdm,xzq,village name,villagedm code,town,towndm,case when  round(COALESCE(ssltdbfjsl,0)*1.00*100/case when zcount =0 then null else zcount end ,2) is null then 0.00 else round(COALESCE(ssltdbfjsl,0)*1.00*100/case when zcount =0 then null else zcount end ,2) end   dbl from  (SELECT 
		t1.town,
		t1.towndm,
    t1.xzq,
    t1.xzqdm,
		t1.village,
		t1.villagedm,
    COALESCE((t1.zcount)::numeric, (0)::numeric) AS zcount,
    COALESCE(t2.ssltdbfjsl, (0)::bigint) AS ssltdbfjsl
FROM (
    (
              SELECT 
       count( case when fwbh in (select distinct fwbh from zm_pc_yhlog_cj where create_on between @startime  AND @endtime::TIMESTAMP ) and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)  then fwbh end) AS zcount,
     max(zm_buildings.xzq) xzq,
     max(zm_buildings.xzqdm) xzqdm,
     max(town) town ,max(towndm) towndm,villagedm,max(village) village
  FROM zm_buildings   
  GROUP BY zm_buildings.villagedm
  ORDER BY zm_buildings.villagedm
    ) AS t1
    LEFT JOIN (
         SELECT 
                count(1) ssltdbfjsl,village
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.village,jsrysstd, t1.fwbh,fwzt 
            FROM  zm_pc_baselog_cj t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE t1.create_on BETWEEN @startime AND @endtime::TIMESTAMP and sfyzc='是'  
        ) AS zm_pc_base where row_num =1   and jsrysstd='是' and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime) GROUP BY village
    ) AS t2 ON t1.villagedm::text = t2.village::text
) 
 where t1.villagedm like @code
) jbxxgqview   ) a ORDER BY dbl desc,code 
 ";
                }
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + param.code + "%", @startime = param.date[0], @endtime = formattedDate }).ToList();
                var res = new RespRet<object>(true, "Ok", data);
                WriteSystemLog("housescjjc/dblbar", res, null);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<object>(false, ex.ToString());
                WriteSystemLogError("housescjjc/dblbar", res, null);
                res.Msg = "系统异常";
                return res;
            }
        }





        /// <summary>
        /// 街镇达标率排名
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("housesjzhc/dblbar")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<object> JzDblbar([FromBody] DateQuery param)
        {
            try
            {
                var usernNumber = this.CurrentUser();
                var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
                var a = JsonRegion(user.District);
                JObject obj = JObject.Parse(user.District);
                string RegionCode = (string)obj["RegionCode"];
                RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0, RegionCode.Length - 3) : RegionCode;
                var isxz = true;
                if (param.date == null || param.date.Count != 2)
                {
                    return new RespRet<object>(false, "时间不能为空");
                }

                if (param.date[1] > DateTime.Now)
                {
                    param.date[1] = DateTime.Today;
                }
                param.date[1] = param.date[1].AddHours(23);
                param.date[1] = param.date[1].AddMinutes(59);
                param.date[1] = param.date[1].AddSeconds(59);
                string formattedDate = param.date[1].ToString("yyyy-MM-dd HH:mm:ss");
                string table_name = "zm_building_fwzt_" + param.date[1].Year;
                if (string.IsNullOrWhiteSpace(param.code))
                {
                    isxz = false;
                    param.code = RegionCode;
                }
                param.code = param.code.Substring(param.code.Length - 3) == "000" ? param.code.Substring(0, param.code.Length - 3) : param.code;

                string sql = @" ";
                if (param.code.Length == 3 || param.code.Length == 6)
                {
                    sql = $@"  
select   zcount,(zcount-ydb) wdb,sjfwzs,ydb,name,code,CASE WHEN dbl > 100 THEN 100.00  when dbl is null then 0.00 ELSE dbl END dbl  from  (select ydb,zcount,sjfwzs,xzq name,xzqdm code,case when  round(ydb*1.00*100/case when zcount =0 then null else zcount end ,2) is null then 0.00 else round(ydb*1.00*100/case when zcount =0 then null else zcount end ,2) end   dbl from  (select max(jbxxgqview.xzq) xzq,jbxxgqview.xzqdm,sum(jbxxgqview.zcount) zcount,sum(ssltdbfjsl) ydb ,sum(sjfwzs) sjfwzs   from (SELECT 
    t1.xzq,
    t1.xzqdm,
    COALESCE((t1.zcount)::numeric, (0)::numeric) AS zcount,
   COALESCE((t1.sjfwzs)::numeric, (0)::numeric) AS sjfwzs,
		COALESCE(t2.ssltdbfjsl, (0)::bigint) AS ssltdbfjsl
FROM (
    (
        SELECT 
             count(1) sjfwzs,
             count( case when fwbh in (select distinct fwbh from zm_pc_yhlog_sqjz where jcly='4'   and  create_on between @startime and @endtime::TIMESTAMP) and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)   then fwbh end ) AS zcount,
            zm_buildings.xzq,
            zm_buildings.xzqdm 
        FROM zm_buildings   
        GROUP BY zm_buildings.xzqdm, zm_buildings.xzq
        ORDER BY zm_buildings.xzqdm
    ) AS t1
    LEFT JOIN (
       SELECT 
                count(1) ssltdbfjsl,xzqdm,xzq
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.create_on, t1.xzq,jsrysstd, t1.xzqdm,t1.jzxyssltsl, t1.fwbh,fwzt 
            FROM  zm_pc_baselog_sqjz t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE  yhly='4'and t1.create_on BETWEEN @startime AND @endtime::TIMESTAMP and sfyzc='是'  
        ) AS zm_pc_base where row_num =1   and jsrysstd='是' and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)  GROUP BY xzqdm,xzq
    ) AS t2 ON t1.xzqdm::text = t2.xzqdm::text
) 
) jbxxgqview  GROUP BY xzqdm ) a ORDER BY dbl desc,xzqdm )a 	
	  ";
                }
                if (param.code.Length == 6 && isxz)
                {
                    sql = $@"  
  select  xzqdm,xzq,sjfwzs,zcount,(zcount-ydb) wdb,ydb,name,code,CASE WHEN dbl > 100 THEN 100.00  when dbl is null then 0.00 ELSE dbl END dbl  from  (select COALESCE(ssltdbfjsl,0) ydb,sjfwzs,zcount,xzqdm,xzq,town name,towndm code,case when  round(COALESCE(ssltdbfjsl,0)*1.00*100/case when zcount =0 then null else zcount end ,2) is null then 0.00 else round(COALESCE(ssltdbfjsl,0)*1.00*100/case when zcount =0 then null else zcount end ,2) end   dbl from  (SELECT 
    t1.xzq,
    t1.xzqdm,
		t1.town,
		t1.towndm,
    COALESCE((t1.zcount)::numeric, (0)::numeric) AS zcount,
   COALESCE((t1.sjfwzs)::numeric, (0)::numeric) AS sjfwzs,
    COALESCE(t2.ssltdbfjsl, (0)::bigint) AS ssltdbfjsl
FROM (
    (
        SELECT 
    count( case when fwbh in (select distinct fwbh from zm_pc_yhlog_sqjz where jcly='4'   and  create_on between @startime and @endtime::TIMESTAMP) and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)   then fwbh end ) AS zcount,
            count(1)   sjfwzs,
                          max(zm_buildings.xzq)xzq,
						 max(zm_buildings.xzqdm)xzqdm,
						  max(zm_buildings.town)town,
            zm_buildings.towndm 
        FROM  zm_buildings
        GROUP BY zm_buildings.towndm
        ORDER BY zm_buildings.towndm
    ) AS t1
    LEFT JOIN (
        SELECT 
                count(1) ssltdbfjsl,town
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.town,jsrysstd, t1.fwbh,fwzt 
            FROM  zm_pc_baselog_sqjz t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE yhly='4' and t1.create_on BETWEEN @startime AND @endtime::TIMESTAMP and sfyzc='是'  
        ) AS zm_pc_base  where row_num =1   and jsrysstd='是' and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)  GROUP BY town
    ) AS t2 ON t1.towndm::text = t2.town::text
) 
 where t1.towndm like @code
) jbxxgqview   ) a ORDER BY dbl desc,code ";
                }
                if (param.code.Length == 9)
                {
                    sql = $@"select zcount,sjfwzs,(zcount-ydb) wdb,town,towndm,xzqdm,xzq,zcount,ydb,name,code,CASE WHEN dbl > 100 THEN 100.00  when dbl is null then 0.00 ELSE dbl END dbl  from  (select COALESCE(ssltdbfjsl,0) ydb,sjfwzs,zcount,xzqdm,xzq,village name,villagedm code,town,towndm,case when  round(COALESCE(ssltdbfjsl,0)*1.00*100/case when zcount =0 then null else zcount end ,2) is null then 0.00 else round(COALESCE(ssltdbfjsl,0)*1.00*100/case when zcount =0 then null else zcount end ,2) end   dbl from  (SELECT 
		t1.town,
		t1.towndm,
    t1.xzq,
    t1.xzqdm,
		t1.village,
		t1.villagedm,
    COALESCE((t1.zcount)::numeric, (0)::numeric) AS zcount,
    COALESCE((t1.sjfwzs)::numeric, (0)::numeric) AS sjfwzs,
    COALESCE(t2.ssltdbfjsl, (0)::bigint) AS ssltdbfjsl
FROM (
    (
        SELECT 
    count( case when fwbh in (select distinct fwbh from zm_pc_yhlog_sqjz where jcly='4'   and  create_on between @startime and @endtime::TIMESTAMP) and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)   then fwbh end ) AS zcount,
				        count(1) sjfwzs,
                        max(zm_buildings.town) town,
						max(zm_buildings.towndm) towndm,
            max(zm_buildings.xzq)xzq,
						 max(zm_buildings.xzqdm)xzqdm,
						  max(zm_buildings.village)village,
            zm_buildings.villagedm 
        FROM zm_buildings 
        GROUP BY zm_buildings.villagedm
        ORDER BY zm_buildings.villagedm
    ) AS t1
    LEFT JOIN (
         SELECT 
                count(1) ssltdbfjsl,village
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.village,jsrysstd, t1.fwbh,fwzt 
            FROM  zm_pc_baselog_sqjz t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE yhly='4' and t1.create_on BETWEEN @startime AND @endtime::TIMESTAMP and sfyzc='是'  
        ) AS zm_pc_base  where row_num =1   and jsrysstd='是' and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)  GROUP BY village
    ) AS t2 ON t1.villagedm::text = t2.village::text
) 
 where t1.villagedm like @code
) jbxxgqview   ) a ORDER BY dbl desc,code 
 ";
                }
                if (param.code.Length == 12)
                {
                    sql = $@" select zcount,(zcount-ydb) wdb,sjfwzs,town,towndm,xzqdm,xzq,zcount,ydb,name,code,CASE WHEN dbl > 100 THEN 100.00  when dbl is null then 0.00 ELSE dbl END dbl  from  (select COALESCE(ssltdbfjsl,0) ydb,sjfwzs,zcount,xzqdm,xzq,village name,villagedm code,town,towndm,case when  round(COALESCE(ssltdbfjsl,0)*1.00*100/case when zcount =0 then null else zcount end ,2) is null then 0.00 else round(COALESCE(ssltdbfjsl,0)*1.00*100/case when zcount =0 then null else zcount end ,2) end   dbl from  (SELECT 
		t1.town,
		t1.towndm,
    t1.xzq,
    t1.xzqdm,
		t1.village,
		t1.villagedm,
    COALESCE((t1.zcount)::numeric, (0)::numeric) AS zcount,
 COALESCE((t1.sjfwzs)::numeric, (0)::numeric) AS sjfwzs,
    COALESCE(t2.ssltdbfjsl, (0)::bigint) AS ssltdbfjsl
FROM (
    (
        SELECT 
    count( case when fwbh in (select distinct fwbh from zm_pc_yhlog_sqjz where jcly='4'   and  create_on between @startime and @endtime::TIMESTAMP) and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)   then fwbh end ) AS zcount,
				count(1) sjfwzs,
                        max(zm_buildings.town) town,
						max(zm_buildings.towndm) towndm,
            max(zm_buildings.xzq)xzq,
						 max(zm_buildings.xzqdm)xzqdm,
						  max(zm_buildings.village)village,
            zm_buildings.villagedm 
        FROM zm_buildings 
        GROUP BY zm_buildings.villagedm
        ORDER BY zm_buildings.villagedm
    ) AS t1
    LEFT JOIN (
         SELECT 
                count(1) ssltdbfjsl,village
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.village,jsrysstd, t1.fwbh,fwzt 
            FROM  zm_pc_baselog_sqjz t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE yhly='4' and t1.create_on BETWEEN @startime AND @endtime::TIMESTAMP and sfyzc='是'  
        ) AS zm_pc_base  where row_num =1   and jsrysstd='是' and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)  GROUP BY village
    ) AS t2 ON t1.villagedm::text = t2.village::text
) 
 where t1.villagedm like @code
) jbxxgqview   ) a ORDER BY dbl desc,code 
 ";
                }
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + param.code + "%", @startime = param.date[0], @endtime =formattedDate}).ToList();
                var res = new RespRet<object>(true, "Ok", data);
                WriteSystemLog("housesjzhc/dblbar", res, null);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<object>(false, ex.ToString());
                WriteSystemLogError("housesjzhc/dblbar", res, null);
                res.Msg = "系统异常";
                return res;
            }
        }











        /// <summary>
        /// 区达标率排名
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("housesqjhc/dblbar")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<object> QjDblbar([FromBody] DateQuery param)
        {
            try
            {
                var usernNumber = this.CurrentUser();
                var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
                var a = JsonRegion(user.District);
                JObject obj = JObject.Parse(user.District);
                string RegionCode = (string)obj["RegionCode"];
                RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0, RegionCode.Length - 3) : RegionCode;
                var isxz = true;
                if (param.date == null || param.date.Count != 2)
                {
                    return new RespRet<object>(false, "时间不能为空");
                }

                if (param.date[1] > DateTime.Now)
                {
                    param.date[1] = DateTime.Today;
                }
                param.date[1] = param.date[1].AddHours(23);
                param.date[1] = param.date[1].AddMinutes(59);
                param.date[1] = param.date[1].AddSeconds(59);
                string formattedDate = param.date[1].ToString("yyyy-MM-dd HH:mm:ss");
                string table_name = "zm_building_fwzt_" + param.date[1].Year;
                if (string.IsNullOrWhiteSpace(param.code))
                {
                    isxz = false;
                    param.code = RegionCode;
                }
                param.code = param.code.Substring(param.code.Length - 3) == "000" ? param.code.Substring(0, param.code.Length - 3) : param.code;

                string sql = @" ";
                if (param.code.Length == 3 || param.code.Length == 6)
                {
                    sql = $@"  
select   zcount,(zcount-ydb) wdb,sjfwzs,ydb,name,code,CASE WHEN dbl > 100 THEN 100.00  when dbl is null then 0.00 ELSE dbl END dbl  from  (select ydb,zcount,sjfwzs,xzq name,xzqdm code,case when  round(ydb*1.00*100/case when zcount =0 then null else zcount end ,2) is null then 0.00 else round(ydb*1.00*100/case when zcount =0 then null else zcount end ,2) end   dbl from  (select max(jbxxgqview.xzq) xzq,jbxxgqview.xzqdm,sum(jbxxgqview.zcount) zcount,sum(ssltdbfjsl) ydb ,sum(sjfwzs) sjfwzs   from (SELECT 
    t1.xzq,
    t1.xzqdm,
    COALESCE((t1.zcount)::numeric, (0)::numeric) AS zcount,
   COALESCE((t1.sjfwzs)::numeric, (0)::numeric) AS sjfwzs,
		COALESCE(t2.ssltdbfjsl, (0)::bigint) AS ssltdbfjsl
FROM (
    (
        SELECT 
             count(1) sjfwzs,
    count( case when fwbh in (select distinct fwbh from zm_pc_yhlog_sqjz where jcly='3'   and  create_on between @startime and @endtime::TIMESTAMP) and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)   then fwbh end ) AS zcount,
            zm_buildings.xzq,
            zm_buildings.xzqdm 
        FROM zm_buildings   
        GROUP BY zm_buildings.xzqdm, zm_buildings.xzq
        ORDER BY zm_buildings.xzqdm
    ) AS t1
    LEFT JOIN (
       SELECT 
                count(1) ssltdbfjsl,xzqdm,xzq
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.create_on, t1.xzq,jsrysstd, t1.xzqdm,t1.jzxyssltsl, t1.fwbh,fwzt 
            FROM  zm_pc_baselog_sqjz t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE  yhly='3'and t1.create_on BETWEEN @startime AND @endtime::TIMESTAMP and sfyzc='是'  
        ) AS zm_pc_base  where row_num =1   and jsrysstd='是' and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)  GROUP BY xzqdm,xzq
    ) AS t2 ON t1.xzqdm::text = t2.xzqdm::text
) 
) jbxxgqview  GROUP BY xzqdm ) a ORDER BY dbl desc,xzqdm )a 	
	  ";
                }
                if (param.code.Length == 6 && isxz)
                {
                    sql = $@"  
  select  xzqdm,xzq,sjfwzs,zcount,(zcount-ydb) wdb,ydb,name,code,CASE WHEN dbl > 100 THEN 100.00  when dbl is null then 0.00 ELSE dbl END dbl  from  (select COALESCE(ssltdbfjsl,0) ydb,sjfwzs,zcount,xzqdm,xzq,town name,towndm code,case when  round(COALESCE(ssltdbfjsl,0)*1.00*100/case when zcount =0 then null else zcount end ,2) is null then 0.00 else round(COALESCE(ssltdbfjsl,0)*1.00*100/case when zcount =0 then null else zcount end ,2) end   dbl from  (SELECT 
    t1.xzq,
    t1.xzqdm,
		t1.town,
		t1.towndm,
    COALESCE((t1.zcount)::numeric, (0)::numeric) AS zcount,
   COALESCE((t1.sjfwzs)::numeric, (0)::numeric) AS sjfwzs,
    COALESCE(t2.ssltdbfjsl, (0)::bigint) AS ssltdbfjsl
FROM (
    (
        SELECT 
    count( case when fwbh in (select distinct fwbh from zm_pc_yhlog_sqjz where jcly='3'   and  create_on between @startime and @endtime::TIMESTAMP) and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)   then fwbh end ) AS zcount,
            count(1)   sjfwzs,
                          max(zm_buildings.xzq)xzq,
						 max(zm_buildings.xzqdm)xzqdm,
						  max(zm_buildings.town)town,
            zm_buildings.towndm 
        FROM  zm_buildings
        GROUP BY zm_buildings.towndm
        ORDER BY zm_buildings.towndm
    ) AS t1
    LEFT JOIN (
        SELECT 
                count(1) ssltdbfjsl,town
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.town,jsrysstd, t1.fwbh,fwzt 
            FROM  zm_pc_baselog_sqjz t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE yhly='3' and t1.create_on BETWEEN @startime AND @endtime::TIMESTAMP and sfyzc='是'  
        ) AS zm_pc_base  where row_num =1   and jsrysstd='是' and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)  GROUP BY town
    ) AS t2 ON t1.towndm::text = t2.town::text
) 
 where t1.towndm like @code
) jbxxgqview   ) a ORDER BY dbl desc,code ";
                }
                if (param.code.Length == 9)
                {
                    sql = $@"select zcount,sjfwzs,(zcount-ydb) wdb,town,towndm,xzqdm,xzq,zcount,ydb,name,code,CASE WHEN dbl > 100 THEN 100.00  when dbl is null then 0.00 ELSE dbl END dbl  from  (select COALESCE(ssltdbfjsl,0) ydb,sjfwzs,zcount,xzqdm,xzq,village name,villagedm code,town,towndm,case when  round(COALESCE(ssltdbfjsl,0)*1.00*100/case when zcount =0 then null else zcount end ,2) is null then 0.00 else round(COALESCE(ssltdbfjsl,0)*1.00*100/case when zcount =0 then null else zcount end ,2) end   dbl from  (SELECT 
		t1.town,
		t1.towndm,
    t1.xzq,
    t1.xzqdm,
		t1.village,
		t1.villagedm,
    COALESCE((t1.zcount)::numeric, (0)::numeric) AS zcount,
    COALESCE((t1.sjfwzs)::numeric, (0)::numeric) AS sjfwzs,
    COALESCE(t2.ssltdbfjsl, (0)::bigint) AS ssltdbfjsl
FROM (
    (
        SELECT 
    count( case when fwbh in (select distinct fwbh from zm_pc_yhlog_sqjz where jcly='3'   and  create_on between @startime and @endtime::TIMESTAMP) and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)   then fwbh end ) AS zcount,
				        count(1) sjfwzs,
                        max(zm_buildings.town) town,
						max(zm_buildings.towndm) towndm,
            max(zm_buildings.xzq)xzq,
						 max(zm_buildings.xzqdm)xzqdm,
						  max(zm_buildings.village)village,
            zm_buildings.villagedm 
        FROM zm_buildings 
        GROUP BY zm_buildings.villagedm
        ORDER BY zm_buildings.villagedm
    ) AS t1
    LEFT JOIN (
         SELECT 
                count(1) ssltdbfjsl,village
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.village,jsrysstd, t1.fwbh,fwzt 
            FROM  zm_pc_baselog_sqjz t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE yhly='3' and t1.create_on BETWEEN @startime AND @endtime::TIMESTAMP and sfyzc='是'  
        ) AS zm_pc_base  where row_num =1   and jsrysstd='是' and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)  GROUP BY village
    ) AS t2 ON t1.villagedm::text = t2.village::text
) 
 where t1.villagedm like @code
) jbxxgqview   ) a ORDER BY dbl desc,code 
 ";
                }
                if (param.code.Length == 12)
                {
                    sql = $@" select zcount,(zcount-ydb) wdb,sjfwzs,town,towndm,xzqdm,xzq,zcount,ydb,name,code,CASE WHEN dbl > 100 THEN 100.00  when dbl is null then 0.00 ELSE dbl END dbl  from  (select COALESCE(ssltdbfjsl,0) ydb,sjfwzs,zcount,xzqdm,xzq,village name,villagedm code,town,towndm,case when  round(COALESCE(ssltdbfjsl,0)*1.00*100/case when zcount =0 then null else zcount end ,2) is null then 0.00 else round(COALESCE(ssltdbfjsl,0)*1.00*100/case when zcount =0 then null else zcount end ,2) end   dbl from  (SELECT 
		t1.town,
		t1.towndm,
    t1.xzq,
    t1.xzqdm,
		t1.village,
		t1.villagedm,
    COALESCE((t1.zcount)::numeric, (0)::numeric) AS zcount,
 COALESCE((t1.sjfwzs)::numeric, (0)::numeric) AS sjfwzs,
    COALESCE(t2.ssltdbfjsl, (0)::bigint) AS ssltdbfjsl
FROM (
    (
        SELECT 
    count( case when fwbh in (select distinct fwbh from zm_pc_yhlog_sqjz where jcly='3'   and  create_on between @startime and @endtime::TIMESTAMP) and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)   then fwbh end ) AS zcount,
				count(1) sjfwzs,
                        max(zm_buildings.town) town,
						max(zm_buildings.towndm) towndm,
            max(zm_buildings.xzq)xzq,
						 max(zm_buildings.xzqdm)xzqdm,
						  max(zm_buildings.village)village,
            zm_buildings.villagedm 
        FROM zm_buildings 
        GROUP BY zm_buildings.villagedm
        ORDER BY zm_buildings.villagedm
    ) AS t1
    LEFT JOIN (
         SELECT 
                count(1) ssltdbfjsl,village
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.village,jsrysstd, t1.fwbh,fwzt 
            FROM  zm_pc_baselog_sqjz t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE yhly='3' and t1.create_on BETWEEN @startime AND @endtime::TIMESTAMP and sfyzc='是'  
        ) AS zm_pc_base  where row_num =1   and jsrysstd='是' and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)  GROUP BY village
    ) AS t2 ON t1.villagedm::text = t2.village::text
) 
 where t1.villagedm like @code
) jbxxgqview   ) a ORDER BY dbl desc,code 
 ";
                }
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + param.code + "%", @startime = param.date[0], @endtime = formattedDate}).ToList();
                var res = new RespRet<object>(true, "Ok", data);
                WriteSystemLog("housesqjhc/dblbar", res, null);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<object>(false, ex.ToString());
                WriteSystemLogError("housesqjhc/dblbar", res, null);
                res.Msg = "系统异常";
                return res;
            }
        }



        /// <summary>
        /// 市核查达标率排名
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("housessjhc/dblbar")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<object> SDblbar([FromBody] DateQuery param)
        {
            try
            {
                var usernNumber = this.CurrentUser();
                var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
                var a = JsonRegion(user.District);
                JObject obj = JObject.Parse(user.District);
                string RegionCode = (string)obj["RegionCode"];
                RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0, RegionCode.Length - 3) : RegionCode;
                var isxz = true;
                if (param.date == null || param.date.Count != 2)
                {
                    return new RespRet<object>(false, "时间不能为空");
                }


                if (param.date[1] > DateTime.Now)
                {
                    param.date[1] = DateTime.Today;
                }
                param.date[1] = param.date[1].AddHours(23);
                param.date[1] = param.date[1].AddMinutes(59);
                param.date[1] = param.date[1].AddSeconds(59);
                string formattedDate = param.date[1].ToString("yyyy-MM-dd HH:mm:ss");
                string table_name = "zm_building_fwzt_" + param.date[1].Year;
                if (string.IsNullOrWhiteSpace(param.code))
                {
                    isxz = false;
                    param.code = RegionCode;
                }
                param.code = param.code.Substring(param.code.Length - 3) == "000" ? param.code.Substring(0, param.code.Length - 3) : param.code;

                string sql = @" ";
                if (param.code.Length == 3 || param.code.Length == 6)
                {
                    sql = $@"  
select   zcount,(zcount-ydb) wdb,sjfwzs,ydb,name,code,CASE WHEN dbl > 100 THEN 100.00  when dbl is null then 0.00 ELSE dbl END dbl  from  (select ydb,zcount,sjfwzs,xzq name,xzqdm code,case when  round(ydb*1.00*100/case when zcount =0 then null else zcount end ,2) is null then 0.00 else round(ydb*1.00*100/case when zcount =0 then null else zcount end ,2) end   dbl from  (select max(jbxxgqview.xzq) xzq,jbxxgqview.xzqdm,sum(jbxxgqview.zcount) zcount,sum(ssltdbfjsl) ydb ,sum(sjfwzs) sjfwzs   from (SELECT 
    t1.xzq,
    t1.xzqdm,
    COALESCE((t1.zcount)::numeric, (0)::numeric) AS zcount,
   COALESCE((t1.sjfwzs)::numeric, (0)::numeric) AS sjfwzs,
		COALESCE(t2.ssltdbfjsl, (0)::bigint) AS ssltdbfjsl
FROM (
    (
        SELECT 
             count(1) sjfwzs,
    count( case when fwbh in (select distinct fwbh from zm_pc_yhlog_sqjz where jcly='2'   and  create_on between @startime and @endtime::TIMESTAMP) and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)   then fwbh end ) AS zcount,
            zm_buildings.xzq,
            zm_buildings.xzqdm 
        FROM zm_buildings   
        GROUP BY zm_buildings.xzqdm, zm_buildings.xzq
        ORDER BY zm_buildings.xzqdm
    ) AS t1
    LEFT JOIN (
       SELECT 
                count(1) ssltdbfjsl,xzqdm,xzq
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.create_on, t1.xzq,jsrysstd, t1.xzqdm,t1.jzxyssltsl, t1.fwbh,fwzt 
            FROM  zm_pc_baselog_sqjz t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE  yhly='2'and t1.create_on BETWEEN @startime AND @endtime::TIMESTAMP and sfyzc='是'  
        ) AS zm_pc_base  where row_num =1   and jsrysstd='是' and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)  GROUP BY xzqdm,xzq
    ) AS t2 ON t1.xzqdm::text = t2.xzqdm::text
) 
) jbxxgqview  GROUP BY xzqdm ) a ORDER BY dbl desc,xzqdm )a 	
	  ";
                }
                if (param.code.Length == 6 && isxz)
                {
                    sql = $@"  
  select  xzqdm,xzq,sjfwzs,zcount,(zcount-ydb) wdb,ydb,name,code,CASE WHEN dbl > 100 THEN 100.00  when dbl is null then 0.00 ELSE dbl END dbl  from  (select COALESCE(ssltdbfjsl,0) ydb,sjfwzs,zcount,xzqdm,xzq,town name,towndm code,case when  round(COALESCE(ssltdbfjsl,0)*1.00*100/case when zcount =0 then null else zcount end ,2) is null then 0.00 else round(COALESCE(ssltdbfjsl,0)*1.00*100/case when zcount =0 then null else zcount end ,2) end   dbl from  (SELECT 
    t1.xzq,
    t1.xzqdm,
		t1.town,
		t1.towndm,
    COALESCE((t1.zcount)::numeric, (0)::numeric) AS zcount,
   COALESCE((t1.sjfwzs)::numeric, (0)::numeric) AS sjfwzs,
    COALESCE(t2.ssltdbfjsl, (0)::bigint) AS ssltdbfjsl
FROM (
    (
        SELECT 
    count( case when fwbh in (select distinct fwbh from zm_pc_yhlog_sqjz where jcly='2'   and  create_on between @startime and @endtime::TIMESTAMP) and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)   then fwbh end ) AS zcount,
            count(1)   sjfwzs,
                          max(zm_buildings.xzq)xzq,
						 max(zm_buildings.xzqdm)xzqdm,
						  max(zm_buildings.town)town,
            zm_buildings.towndm 
        FROM  zm_buildings
        GROUP BY zm_buildings.towndm
        ORDER BY zm_buildings.towndm
    ) AS t1
    LEFT JOIN (
        SELECT 
                count(1) ssltdbfjsl,town
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.town,jsrysstd, t1.fwbh,fwzt 
            FROM  zm_pc_baselog_sqjz t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE yhly='2' and t1.create_on BETWEEN @startime AND @endtime::TIMESTAMP and sfyzc='是'  
        ) AS zm_pc_base  where row_num =1   and jsrysstd='是' and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)  GROUP BY town
    ) AS t2 ON t1.towndm::text = t2.town::text
) 
 where t1.towndm like @code
) jbxxgqview   ) a ORDER BY dbl desc,code ";
                }
                if (param.code.Length == 9)
                {
                    sql = $@"select zcount,sjfwzs,(zcount-ydb) wdb,town,towndm,xzqdm,xzq,zcount,ydb,name,code,CASE WHEN dbl > 100 THEN 100.00  when dbl is null then 0.00 ELSE dbl END dbl  from  (select COALESCE(ssltdbfjsl,0) ydb,sjfwzs,zcount,xzqdm,xzq,village name,villagedm code,town,towndm,case when  round(COALESCE(ssltdbfjsl,0)*1.00*100/case when zcount =0 then null else zcount end ,2) is null then 0.00 else round(COALESCE(ssltdbfjsl,0)*1.00*100/case when zcount =0 then null else zcount end ,2) end   dbl from  (SELECT 
		t1.town,
		t1.towndm,
    t1.xzq,
    t1.xzqdm,
		t1.village,
		t1.villagedm,
    COALESCE((t1.zcount)::numeric, (0)::numeric) AS zcount,
    COALESCE((t1.sjfwzs)::numeric, (0)::numeric) AS sjfwzs,
    COALESCE(t2.ssltdbfjsl, (0)::bigint) AS ssltdbfjsl
FROM (
    (
        SELECT 
    count( case when fwbh in (select distinct fwbh from zm_pc_yhlog_sqjz where jcly='2'   and  create_on between @startime and @endtime::TIMESTAMP) and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)   then fwbh end ) AS zcount,
				        count(1) sjfwzs,
                        max(zm_buildings.town) town,
						max(zm_buildings.towndm) towndm,
            max(zm_buildings.xzq)xzq,
						 max(zm_buildings.xzqdm)xzqdm,
						  max(zm_buildings.village)village,
            zm_buildings.villagedm 
        FROM zm_buildings 
        GROUP BY zm_buildings.villagedm
        ORDER BY zm_buildings.villagedm
    ) AS t1
    LEFT JOIN (
         SELECT 
                count(1) ssltdbfjsl,village
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.village,jsrysstd, t1.fwbh,fwzt 
            FROM  zm_pc_baselog_sqjz t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE yhly='2' and t1.create_on BETWEEN @startime AND @endtime::TIMESTAMP and sfyzc='是'  
        ) AS zm_pc_base  where row_num =1   and jsrysstd='是' and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)  GROUP BY village
    ) AS t2 ON t1.villagedm::text = t2.village::text
) 
 where t1.villagedm like @code
) jbxxgqview   ) a ORDER BY dbl desc,code 
 ";
                }
                if (param.code.Length == 12)
                {
                    sql = $@"select zcount,(zcount-ydb) wdb,sjfwzs,town,towndm,xzqdm,xzq,zcount,ydb,name,code,CASE WHEN dbl > 100 THEN 100.00  when dbl is null then 0.00 ELSE dbl END dbl  from  (select COALESCE(ssltdbfjsl,0) ydb,sjfwzs,zcount,xzqdm,xzq,village name,villagedm code,town,towndm,case when  round(COALESCE(ssltdbfjsl,0)*1.00*100/case when zcount =0 then null else zcount end ,2) is null then 0.00 else round(COALESCE(ssltdbfjsl,0)*1.00*100/case when zcount =0 then null else zcount end ,2) end   dbl from  (SELECT 
		t1.town,
		t1.towndm,
    t1.xzq,
    t1.xzqdm,
		t1.village,
		t1.villagedm,
    COALESCE((t1.zcount)::numeric, (0)::numeric) AS zcount,
 COALESCE((t1.sjfwzs)::numeric, (0)::numeric) AS sjfwzs,
    COALESCE(t2.ssltdbfjsl, (0)::bigint) AS ssltdbfjsl
FROM (
    (
        SELECT 
    count( case when fwbh in (select distinct fwbh from zm_pc_yhlog_sqjz where jcly='2'   and  create_on between @startime and @endtime::TIMESTAMP) and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)   then fwbh end ) AS zcount,
				count(1) sjfwzs,
                        max(zm_buildings.town) town,
						max(zm_buildings.towndm) towndm,
            max(zm_buildings.xzq)xzq,
						 max(zm_buildings.xzqdm)xzqdm,
						  max(zm_buildings.village)village,
            zm_buildings.villagedm 
        FROM zm_buildings 
        GROUP BY zm_buildings.villagedm
        ORDER BY zm_buildings.villagedm
    ) AS t1
    LEFT JOIN (
         SELECT 
                count(1) ssltdbfjsl,village
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.village,jsrysstd, t1.fwbh,fwzt 
            FROM  zm_pc_baselog_sqjz t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE yhly='2' and t1.create_on BETWEEN @startime AND @endtime::TIMESTAMP and sfyzc='是'  
        ) AS zm_pc_base  where row_num =1   and jsrysstd='是' and fwbh not in (select fwbh From {table_name} where final_fwzt is not null and final_create_on=@endtime)  GROUP BY village
    ) AS t2 ON t1.villagedm::text = t2.village::text
) 
 where t1.villagedm like @code
) jbxxgqview   ) a ORDER BY dbl desc,code 
 ";
                }
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + param.code + "%", @startime = param.date[0], @endtime =formattedDate }).ToList();
                var res = new RespRet<object>(true, "Ok", data);
                WriteSystemLog("housessjhc/dblbar", res, null);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<object>(false, ex.ToString());
                WriteSystemLogError("housessjhc/dblbar", res, null);
                res.Msg = "系统异常";
                return res;
            }
        }





        /// <summary>
        /// 信息统计 -村级检查达标、未达标房屋
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("housescjjc/searchcj-dbfw")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<Page<ZmPcYhLogCjDto2>>), 200)]
        public RespRet<Page<ZmPcYhLogCjDto>> SearchAttrList2([FromBody] FwlbTj pageRequest)
        {

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<ZmPcYhLogCjDto>>(false, "user not logined");
            }
            try
            {
                if (string.IsNullOrWhiteSpace(pageRequest.code))
                {
                    return new RespRet<Page<ZmPcYhLogCjDto>>(false, "村代码不能为空");
                }
                if (pageRequest.isdb != 1 && pageRequest.isdb != 2)
                {
                    return new RespRet<Page<ZmPcYhLogCjDto>>(false, "请选择正确类型");
                }

                if (pageRequest.date == null || pageRequest.date.Count != 2)
                {
                    return new RespRet<Page<ZmPcYhLogCjDto>>(false, "请选择时间");
                }
                pageRequest.date[1] = pageRequest.date[1].AddHours(23);
                pageRequest.date[1] = pageRequest.date[1].AddMinutes(59);
                pageRequest.date[1] = pageRequest.date[1].AddSeconds(59);

                var sbSql = new StringBuilder();
                var sbCountSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sbSqlWhere2 = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();

                sbSql.Append(@$" select address,hzxm,fwbh from  zm_buildings where fwbh in( SELECT 
          
                DISTINCT CASE when jsrysstd='是' THEN fwbh
                END
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.create_on,t1.village,t1.jsrysstd, t1.fwbh
             FROM zm_pc_yh_cj t0  
     JOIN zm_pc_baselog_cj t1 ON t0.fwbh = t1.fwbh 
     LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh = t2.fwbh 
     JOIN zm_buildings t5 ON t0.fwbh = t5.fwbh 
            WHERE t1.create_on BETWEEN @startime AND @endtime AND t2.fwzt IS NULL  and t1.village=@code 
        ) AS zm_pc_base  where row_num =1  and fwbh not in (select fwbh From zm_pc_base_sqjz where fwzt is not null and yhly='4')
       and village=@code) ");
                sbCountSql.Append(@$" select count(1) count from  zm_buildings where fwbh in( SELECT 
          
               DISTINCT CASE when jsrysstd='是' THEN fwbh 
                END
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.create_on,t1.village,t1.jsrysstd, t1.fwbh
             FROM zm_pc_yh_cj t0  
     JOIN zm_pc_baselog_cj t1 ON t0.fwbh = t1.fwbh 
     LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh = t2.fwbh 
     JOIN zm_buildings t5 ON t0.fwbh = t5.fwbh 
            WHERE t1.create_on BETWEEN @startime AND @endtime AND t2.fwzt IS NULL and t1.village=@code 
        ) AS zm_pc_base  where row_num =1  and fwbh not in (select fwbh From zm_pc_base_sqjz where fwzt is not null and yhly='4')
       and village=@code)  ");
                var count = _dbContext.Database.GetDbConnection().Query<int>(sbCountSql.ToString(), new { @code = pageRequest.code, @startime = pageRequest.date[0], @endtime = pageRequest.date[1] }).FirstOrDefault();
                sbSql.Append($@" order by fwbh ");
                sbSql.Append($@" limit @limit offset @offset");
                sqlParams.Add(new NpgsqlParameter("code", pageRequest.code));
                sqlParams.Add(new NpgsqlParameter("limit", pageRequest.Limit));
                sqlParams.Add(new NpgsqlParameter("offset", pageRequest.Limit * pageRequest.PageIndex));
                sqlParams.Add(new NpgsqlParameter("startime", pageRequest.date[0]));
                sqlParams.Add(new NpgsqlParameter("endtime", pageRequest.date[1]));
                if (pageRequest.isdb != 1)
                {
                    sbSql = new StringBuilder();
                    sbCountSql = new StringBuilder();
                    sbSqlWhere = new StringBuilder();
                    sbSqlWhere2 = new StringBuilder();
                    sqlParams = new List<NpgsqlParameter>();

                    sbSql.Append(@$" select address,hzxm,fwbh from  zm_buildings where fwbh in( SELECT 
          
                DISTINCT CASE when jsrysstd='否' THEN fwbh
                END
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.create_on,t1.village,t1.jsrysstd, t1.fwbh
             FROM zm_pc_yh_cj t0  
     JOIN zm_pc_baselog_cj t1 ON t0.fwbh = t1.fwbh 
     LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh = t2.fwbh 
     JOIN zm_buildings t5 ON t0.fwbh = t5.fwbh 
            WHERE t1.create_on BETWEEN @startime AND @endtime AND t2.fwzt IS NULL  and t1.village=@code 
        ) AS zm_pc_base  where row_num =1  and fwbh not in (select fwbh From zm_pc_base_sqjz where fwzt is not null and yhly='4')
       and village=@code) ");
                    sbCountSql.Append(@$"  select count(1) count from  zm_buildings where fwbh in( SELECT 
          
               DISTINCT CASE when jsrysstd='否' THEN fwbh 
                END
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.create_on,t1.village,t1.jsrysstd, t1.fwbh
             FROM zm_pc_yh_cj t0  
     JOIN zm_pc_baselog_cj t1 ON t0.fwbh = t1.fwbh 
     LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh = t2.fwbh 
     JOIN zm_buildings t5 ON t0.fwbh = t5.fwbh 
            WHERE t1.create_on BETWEEN @startime AND @endtime AND t2.fwzt IS NULL and t1.village=@code 
        ) AS zm_pc_base  where row_num =1  and fwbh not in (select fwbh From zm_pc_base_sqjz where fwzt is not null and yhly='4')
       and village=@code)  ");
                    count = _dbContext.Database.GetDbConnection().Query<int>(sbCountSql.ToString(), new { @code = pageRequest.code, @startime = pageRequest.date[0], @endtime = pageRequest.date[1] }).FirstOrDefault();
                    sbSql.Append($@" order by fwbh ");
                    sbSql.Append($@" limit @limit offset @offset");
                    sqlParams.Add(new NpgsqlParameter("code", pageRequest.code));
                    sqlParams.Add(new NpgsqlParameter("limit", pageRequest.Limit));
                    sqlParams.Add(new NpgsqlParameter("offset", pageRequest.Limit * pageRequest.PageIndex));
                    sqlParams.Add(new NpgsqlParameter("startime", pageRequest.date[0]));
                    sqlParams.Add(new NpgsqlParameter("endtime", pageRequest.date[1]));
                }
                var datas = _dbContext.ZmPcYhLogCjDto.FromSqlRaw(sbSql.ToString(), sqlParams.ToArray()).ToList();
                var pageData = new Page<ZmPcYhLogCjDto>(datas
                    , pageRequest.PageIndex + 1, pageRequest.Limit, count);
                var r = new RespRet<Page<ZmPcYhLogCjDto>>(true, "Ok", pageData);
                WriteSystemLog("housescjjc/searchcj-dbfw", r, pageRequest);
                return r;
            }
            catch (Exception ex)
            {
                var res = new RespRet<Page<ZmPcYhLogCjDto>>(false, ex.ToString());
                WriteSystemLogError("housescjjc/searchcj-dbfw", res, pageRequest);
                res.Msg = "系统异常";
                return res;
            }
        }





        /// <summary>
        /// 信息统计 --街镇核查达标、未达标房屋
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("housesjzhc/searchcj-dbfw")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<Page<ZmPcYhLogCjDto2>>), 200)]
        public RespRet<Page<ZmPcYhLogCjDto>> SearchAttrList3([FromBody] FwlbTj pageRequest)
        {

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<ZmPcYhLogCjDto>>(false, "user not logined");
            }
            try
            {
                if (string.IsNullOrWhiteSpace(pageRequest.code))
                {
                    return new RespRet<Page<ZmPcYhLogCjDto>>(false, "村代码不能为空");
                }
                if (pageRequest.isdb != 1 && pageRequest.isdb != 2)
                {
                    return new RespRet<Page<ZmPcYhLogCjDto>>(false, "请选择正确类型");
                }

                if (pageRequest.date == null || pageRequest.date.Count != 2)
                {
                    return new RespRet<Page<ZmPcYhLogCjDto>>(false, "请选择时间");
                }
                pageRequest.date[1] = pageRequest.date[1].AddHours(23);
                pageRequest.date[1] = pageRequest.date[1].AddMinutes(59);
                pageRequest.date[1] = pageRequest.date[1].AddSeconds(59);

                var sbSql = new StringBuilder();
                var sbCountSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sbSqlWhere2 = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();

                sbSql.Append(@$" select address,hzxm,fwbh from  zm_buildings where    fwbh in( SELECT 
          
             DISTINCT CASE when jsrysstd='是' THEN fwbh end
        FROM (
                        SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.create_on,t1.village,t1.jsrysstd, t1.fwbh
             FROM zm_pc_yh_sqjz t0  
     JOIN zm_pc_baselog_sqjz t1 ON t0.fwbh = t1.fwbh 
 LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh = t2.fwbh
     JOIN zm_buildings t5 ON t0.fwbh = t5.fwbh 
            WHERE t1.create_on BETWEEN @startime AND @endtime AND t0.jcly='4' and t1.yhly='4' and t2.fwzt is  null
        ) AS zm_pc_base  where row_num =1  and fwbh not in (select fwbh From zm_pc_base_sqjz where fwzt is not null and yhly='4')
       and village=@code) ");
                sbCountSql.Append(@$" select count(1) count from  zm_buildings where fwbh in( SELECT 
          
              DISTINCT CASE when jsrysstd='是' THEN fwbh end
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.create_on,t1.village,t1.jsrysstd, t1.fwbh
             FROM zm_pc_yh_sqjz t0  
     JOIN zm_pc_baselog_sqjz t1 ON t0.fwbh = t1.fwbh 
LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh = t2.fwbh
     JOIN zm_buildings t5 ON t0.fwbh = t5.fwbh 
            WHERE t1.create_on BETWEEN @startime AND @endtime AND t0.jcly='4' and t1.yhly='4' and t2.fwzt is null
        ) AS zm_pc_base  where row_num =1  and fwbh not in (select fwbh From zm_pc_base_sqjz where fwzt is not null and yhly='4')
       and village=@code)  ");
                var count = _dbContext.Database.GetDbConnection().Query<int>(sbCountSql.ToString(), new { @code = pageRequest.code, @startime = pageRequest.date[0], @endtime = pageRequest.date[1] }).FirstOrDefault();
                sbSql.Append($@" order by fwbh ");
                sbSql.Append($@" limit @limit offset @offset");
                sqlParams.Add(new NpgsqlParameter("code", pageRequest.code));
                sqlParams.Add(new NpgsqlParameter("limit", pageRequest.Limit));
                sqlParams.Add(new NpgsqlParameter("offset", pageRequest.Limit * pageRequest.PageIndex));
                sqlParams.Add(new NpgsqlParameter("startime", pageRequest.date[0]));
                sqlParams.Add(new NpgsqlParameter("endtime", pageRequest.date[1]));
                if (pageRequest.isdb != 1)
                {
                    sbSql = new StringBuilder();
                    sbCountSql = new StringBuilder();
                    sbSqlWhere = new StringBuilder();
                    sbSqlWhere2 = new StringBuilder();
                    sqlParams = new List<NpgsqlParameter>();
                    sbSql.Append(@$"  select address,hzxm,fwbh from  zm_buildings where    fwbh in( SELECT 
          
             DISTINCT CASE when jsrysstd='否' THEN fwbh end
        FROM (
                        SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.create_on,t1.village,t1.jsrysstd, t1.fwbh
             FROM zm_pc_yh_sqjz t0  
     JOIN zm_pc_baselog_sqjz t1 ON t0.fwbh = t1.fwbh 
 LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh = t2.fwbh
     JOIN zm_buildings t5 ON t0.fwbh = t5.fwbh 
            WHERE t1.create_on BETWEEN @startime AND @endtime AND t0.jcly='4' and t1.yhly='4' and t2.fwzt is  null
        ) AS zm_pc_base  where row_num =1  and fwbh not in (select fwbh From zm_pc_base_sqjz where fwzt is not null and yhly='4')
       and village=@code) ");
                    sbCountSql.Append(@$" select count(1) count from  zm_buildings where fwbh in( SELECT 
          
              DISTINCT CASE when jsrysstd='否' THEN fwbh end
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.create_on,t1.village,t1.jsrysstd, t1.fwbh
             FROM zm_pc_yh_sqjz t0  
     JOIN zm_pc_baselog_sqjz t1 ON t0.fwbh = t1.fwbh 
LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh = t2.fwbh
     JOIN zm_buildings t5 ON t0.fwbh = t5.fwbh 
            WHERE t1.create_on BETWEEN @startime AND @endtime AND t0.jcly='4' and t1.yhly='4' and t2.fwzt is null
        ) AS zm_pc_base  where row_num =1  and fwbh not in (select fwbh From zm_pc_base_sqjz where fwzt is not null and yhly='4')
       and village=@code)   ");
                    count = _dbContext.Database.GetDbConnection().Query<int>(sbCountSql.ToString(), new { @code = pageRequest.code, @startime = pageRequest.date[0], @endtime = pageRequest.date[1] }).FirstOrDefault();
                    sbSql.Append($@" order by fwbh ");
                    sbSql.Append($@" limit @limit offset @offset");
                    sqlParams.Add(new NpgsqlParameter("code", pageRequest.code));
                    sqlParams.Add(new NpgsqlParameter("limit", pageRequest.Limit));
                    sqlParams.Add(new NpgsqlParameter("offset", pageRequest.Limit * pageRequest.PageIndex));
                    sqlParams.Add(new NpgsqlParameter("startime", pageRequest.date[0]));
                    sqlParams.Add(new NpgsqlParameter("endtime", pageRequest.date[1]));
                }
                var datas = _dbContext.ZmPcYhLogCjDto.FromSqlRaw(sbSql.ToString(), sqlParams.ToArray()).ToList();
                var pageData = new Page<ZmPcYhLogCjDto>(datas
                    , pageRequest.PageIndex + 1, pageRequest.Limit, count);
                var r = new RespRet<Page<ZmPcYhLogCjDto>>(true, "Ok", pageData);
                WriteSystemLog("housesjzhc/searchcj-dbfw", r, pageRequest);
                return r;
            }
            catch (Exception ex)
            {
                var res = new RespRet<Page<ZmPcYhLogCjDto>>(false, ex.ToString());
                WriteSystemLogError("housesjzhc/searchcj-dbfw", res, pageRequest);
                res.Msg = "系统异常";
                return res;


            }
        }





        /// <summary>
        /// 信息统计 -区级核查达标、未达标房屋
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("housesqjhc/searchcj-dbfw")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<Page<ZmPcYhLogCjDto2>>), 200)]
        public RespRet<Page<ZmPcYhLogCjDto>> SearchAttrList4([FromBody] FwlbTj pageRequest)
        {

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<ZmPcYhLogCjDto>>(false, "user not logined");
            }
            try
            {
                if (string.IsNullOrWhiteSpace(pageRequest.code))
                {
                    return new RespRet<Page<ZmPcYhLogCjDto>>(false, "村代码不能为空");
                }
                if (pageRequest.isdb != 1 && pageRequest.isdb != 2)
                {
                    return new RespRet<Page<ZmPcYhLogCjDto>>(false, "请选择正确类型");
                }

                if (pageRequest.date == null || pageRequest.date.Count != 2)
                {
                    return new RespRet<Page<ZmPcYhLogCjDto>>(false, "请选择时间");
                }
                pageRequest.date[1] = pageRequest.date[1].AddHours(23);
                pageRequest.date[1] = pageRequest.date[1].AddMinutes(59);
                pageRequest.date[1] = pageRequest.date[1].AddSeconds(59);

                var sbSql = new StringBuilder();
                var sbCountSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sbSqlWhere2 = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();

                sbSql.Append(@$" select address,hzxm,fwbh from  zm_buildings where fwbh in( SELECT 
          
             DISTINCT CASE when jsrysstd='是' THEN fwbh  end
        FROM (
                        SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.create_on,t1.village,t1.jsrysstd, t1.fwbh
             FROM zm_pc_yh_sqjz t0  
     JOIN zm_pc_baselog_sqjz t1 ON t0.fwbh = t1.fwbh 
     LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh = t2.fwbh
     JOIN zm_buildings t5 ON t0.fwbh = t5.fwbh 
            WHERE t1.create_on BETWEEN @startime AND @endtime AND t0.jcly='3' and t1.yhly='3' and t2.fwzt is  null 
        ) AS zm_pc_base  where row_num =1  and fwbh not in (select fwbh From zm_pc_base_sqjz where fwzt is not null and yhly='4')
       and village=@code)  ");
                sbCountSql.Append(@$" select count(1) count from  zm_buildings where fwbh  in( SELECT 
          DISTINCT CASE when jsrysstd='是' THEN fwbh end
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.create_on,t1.village,t1.jsrysstd, t1.fwbh
             FROM zm_pc_yh_sqjz t0  
     JOIN zm_pc_baselog_sqjz t1 ON t0.fwbh = t1.fwbh 
     LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh = t2.fwbh
     JOIN zm_buildings t5 ON t0.fwbh = t5.fwbh 
            WHERE t1.create_on BETWEEN @startime AND @endtime AND t0.jcly='3' and t1.yhly='3' and t2.fwzt is null
        ) AS zm_pc_base  where row_num =1  and fwbh not in (select fwbh From zm_pc_base_sqjz where fwzt is not null and yhly='4')
       and village=@code)  ");
                var count = _dbContext.Database.GetDbConnection().Query<int>(sbCountSql.ToString(), new { @code = pageRequest.code, @startime = pageRequest.date[0], @endtime = pageRequest.date[1] }).FirstOrDefault();
                sbSql.Append($@" order by fwbh ");
                sbSql.Append($@" limit @limit offset @offset");
                sqlParams.Add(new NpgsqlParameter("code", pageRequest.code));
                sqlParams.Add(new NpgsqlParameter("limit", pageRequest.Limit));
                sqlParams.Add(new NpgsqlParameter("offset", pageRequest.Limit * pageRequest.PageIndex));
                sqlParams.Add(new NpgsqlParameter("startime", pageRequest.date[0]));
                sqlParams.Add(new NpgsqlParameter("endtime", pageRequest.date[1]));
                if (pageRequest.isdb != 1)
                {
                    sbSql = new StringBuilder();
                    sbCountSql = new StringBuilder();
                    sbSqlWhere = new StringBuilder();
                    sbSqlWhere2 = new StringBuilder();
                    sqlParams = new List<NpgsqlParameter>();

                    sbSql.Append(@$" select address,hzxm,fwbh from  zm_buildings where fwbh in( SELECT 
          
             DISTINCT CASE when jsrysstd='否' THEN fwbh  end
        FROM (
                        SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.create_on,t1.village,t1.jsrysstd, t1.fwbh
             FROM zm_pc_yh_sqjz t0  
     JOIN zm_pc_baselog_sqjz t1 ON t0.fwbh = t1.fwbh 
     LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh = t2.fwbh
     JOIN zm_buildings t5 ON t0.fwbh = t5.fwbh 
            WHERE t1.create_on BETWEEN @startime AND @endtime AND t0.jcly='3' and t1.yhly='3' and t2.fwzt is  null 
        ) AS zm_pc_base  where row_num =1  and fwbh not in (select fwbh From zm_pc_base_sqjz where fwzt is not null and yhly='4')
       and village=@code)  ");
                    sbCountSql.Append(@$" select count(1) count from  zm_buildings where fwbh  in( SELECT 
          DISTINCT CASE when jsrysstd='否' THEN fwbh end
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.create_on,t1.village,t1.jsrysstd, t1.fwbh
             FROM zm_pc_yh_sqjz t0  
     JOIN zm_pc_baselog_sqjz t1 ON t0.fwbh = t1.fwbh 
     LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh = t2.fwbh
     JOIN zm_buildings t5 ON t0.fwbh = t5.fwbh 
            WHERE t1.create_on BETWEEN @startime AND @endtime AND t0.jcly='3' and t1.yhly='3' and t2.fwzt is null
        ) AS zm_pc_base  where row_num =1  and fwbh not in (select fwbh From zm_pc_base_sqjz where fwzt is not null and yhly='4')
       and village=@code)  ");
                    count = _dbContext.Database.GetDbConnection().Query<int>(sbCountSql.ToString(), new { @code = pageRequest.code, @startime = pageRequest.date[0], @endtime = pageRequest.date[1] }).FirstOrDefault();
                    sbSql.Append($@" order by fwbh ");
                    sbSql.Append($@" limit @limit offset @offset");
                    sqlParams.Add(new NpgsqlParameter("code", pageRequest.code));
                    sqlParams.Add(new NpgsqlParameter("limit", pageRequest.Limit));
                    sqlParams.Add(new NpgsqlParameter("offset", pageRequest.Limit * pageRequest.PageIndex));
                    sqlParams.Add(new NpgsqlParameter("startime", pageRequest.date[0]));
                    sqlParams.Add(new NpgsqlParameter("endtime", pageRequest.date[1]));
                }
                var datas = _dbContext.ZmPcYhLogCjDto.FromSqlRaw(sbSql.ToString(), sqlParams.ToArray()).ToList();
                var pageData = new Page<ZmPcYhLogCjDto>(datas
                    , pageRequest.PageIndex + 1, pageRequest.Limit, count);
                var r = new RespRet<Page<ZmPcYhLogCjDto>>(true, "Ok", pageData);
                WriteSystemLog("housesqjhc/searchcj-dbfw", r, pageRequest);
                return r;
            }
            catch (Exception ex)
            {
                var res = new RespRet<Page<ZmPcYhLogCjDto>>(false, ex.ToString());
                WriteSystemLogError("housesqjhc/searchcj-dbfw", res, pageRequest);
                res.Msg = "系统异常";
                return res;


            }
        }




        /// <summary>
        /// 信息统计 -市级核查达标、未达标房屋
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("housessjhc/searchcj-dbfw")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<Page<ZmPcYhLogCjDto2>>), 200)]
        public RespRet<Page<ZmPcYhLogCjDto>> SearchAttrList5([FromBody] FwlbTj pageRequest)
        {

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<ZmPcYhLogCjDto>>(false, "user not logined");
            }
            try
            {
                if (string.IsNullOrWhiteSpace(pageRequest.code))
                {
                    return new RespRet<Page<ZmPcYhLogCjDto>>(false, "村代码不能为空");
                }
                string str = "";
                if (pageRequest.isdb != 1 && pageRequest.isdb != 2)
                {
                    return new RespRet<Page<ZmPcYhLogCjDto>>(false, "请选择正确类型");
                }

                if (pageRequest.date == null || pageRequest.date.Count != 2)
                {
                    return new RespRet<Page<ZmPcYhLogCjDto>>(false, "请选择时间");
                }
                if (pageRequest.isdb == 1 )
                {
                    str = "and jsrysstd='是'";
                }
                if (pageRequest.isdb == 2)
                {
                    str = "and jsrysstd='否'";
                }
                //处理起始时间
                pageRequest.date[1] = pageRequest.date[1].AddHours(23);
                pageRequest.date[1] = pageRequest.date[1].AddMinutes(59);
                pageRequest.date[1] = pageRequest.date[1].AddSeconds(59);
                string startDate = pageRequest.date[0].ToString("yyyy-MM-dd");
                string endDate = pageRequest.date[1].ToString("yyyy-MM-dd HH:mm:ss");
                string table_fwzt = "zm_building_fwzt_" + pageRequest.date[1].Year;

                //处理村代码
                string villagedm = pageRequest.code;
                int limit = pageRequest.Limit;
                int offset = pageRequest.Limit * pageRequest.PageIndex;


                var sbSql = new StringBuilder();
                var sbCountSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sbSqlWhere2 = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();

                sbSql.Append(@$" select  address,hzxm,fwbh  from (select * from (select ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num, t1.xzq,t1.xzqdm,t1.towndm,t1.town,t1.xzq,t1.villagedm,t1.village,t1.hzxm,t1.address,t1.fwbh,t1.cs,t1.jsrysstd,t1.create_on,t1.create_name,t1.yhly from zm_pc_baselog_sqjz t1,zm_pc_yhlog_sqjz t2 where t1.id=t2.base_id and t1.yhly='2' and t1.create_on BETWEEN '"+ startDate + "' and '"+ endDate + "') t   where row_num=1 and yhly='2' and create_on BETWEEN '" + startDate + "' and '" + endDate + "'  and fwbh not in(select distinct fwbh from "+table_fwzt+" where final_fwzt is not null and   final_create_on='" + endDate + "') and village='"+ villagedm + "'"+str+ "  order by create_on desc,xzqdm ,village asc ) t  order by fwbh limit "+limit+" offset "+offset);
                sbCountSql.Append(@$"select  count(1) count  from (select * from (select ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num, t1.xzq,t1.xzqdm,t1.towndm,t1.town,t1.xzq,t1.villagedm,t1.village,t1.hzxm,t1.address,t1.fwbh,t1.cs,t1.jsrysstd,t1.create_on,t1.create_name,t1.yhly from zm_pc_baselog_sqjz t1,zm_pc_yhlog_sqjz t2 where t1.id=t2.base_id and t1.yhly='2' and t1.create_on BETWEEN '" + startDate + "' and '" + endDate + "') t   where row_num=1 and yhly='2' and create_on BETWEEN '" + startDate + "' and '" + endDate + "'  and fwbh not in(select distinct fwbh from  "+table_fwzt+"  where final_fwzt is not null and   final_create_on='" + endDate + "') and village='"+ villagedm + "'" + str + "   order by create_on desc,xzqdm ,village asc ) t   ");
                var count = _dbContext.Database.GetDbConnection().Query<int>(sbCountSql.ToString(), new { @code = pageRequest.code, @startime = pageRequest.date[0], @endtime = pageRequest.date[1] }).FirstOrDefault();
                
            
                var datas = _dbContext.ZmPcYhLogCjDto.FromSqlRaw(sbSql.ToString(), sqlParams.ToArray()).ToList();
                var pageData = new Page<ZmPcYhLogCjDto>(datas
                    , pageRequest.PageIndex + 1, pageRequest.Limit, count);
                var r = new RespRet<Page<ZmPcYhLogCjDto>>(true, "Ok", pageData);
                WriteSystemLog("housessjhc/searchcj-dbfw", r, pageRequest);
                return r;
            }
            catch (Exception ex)
            {
                var res = new RespRet<Page<ZmPcYhLogCjDto>>(false, ex.ToString());
                WriteSystemLogError("housessjhc/searchcj-dbfw", res, pageRequest);
                res.Msg = "系统异常";
                return res;


            }
        }

        /// <summary>
        /// 核查统计 今日自查情况
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        [HttpPost("houseshc/zctj")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> Zctj([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            if (string.IsNullOrWhiteSpace(usernNumber))
            {
                return new RespRet<object>(false, "用户没有登录");
            }
            try
            {
                if (param == null || param.date == null || param.date.Count != 2)
                {
                    return new RespRet<object>(false, "请传递时间");
                }
                var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
                string sql = "";
                var a = JsonRegion(user.District);
                JObject obj = JObject.Parse(user.District);
                string RegionCode = (string)obj["RegionCode"];
                RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0, RegionCode.Length - 3) : RegionCode;


                if (param.date[1] > DateTime.Now)
                {
                    param.date[1] = DateTime.Today;
                }

                param.date[1] = param.date[1].AddHours(23);
                param.date[1] = param.date[1].AddSeconds(59);
                param.date[1] = param.date[1].AddMinutes(59);
                // 假设 param.date[1] 已经被设置为 2025-06-14 23:59:59
                string formattedDate = param.date[1].ToString("yyyy-MM-dd HH:mm:ss");
                string table_name = "zm_building_fwzt_" + param.date[1].Year;


                if (RegionCode.Length == 3 || RegionCode.Length == 6)
                {
                    sql = @" select COALESCE(wzccount,0) wzccount,COALESCE(zcount,0) zcount ,COALESCE(zccount,0) zccount ,COALESCE(yhcount,0) yhcount ,COALESCE(zgcount,0) zgcount ,case when value >100 then 100.00||'%' WHEN value IS NULL THEN 0||'%' else value||'%' end zcl,case when value >100 then 100.00 WHEN value IS NULL THEN 0 else value end value   from   (select sum(whccount) wzccount,sum(zcount) zcount,sum(hccount) zccount,sum(yhcount) yhcount,sum(zgcount) zgcount,round(sum(hccount)*1.00*100/sum(zcount),2)||'%' zcl, round(sum(hccount)*1.00*100/sum(zcount),2) value from hcqkgqtext(@startime,@endtime::TIMESTAMP,@yhly,@tablename))a
 ";
                }
                if (RegionCode.Length == 9)
                {
                    sql = @" select COALESCE(wzccount,0) wzccount,COALESCE(zcount,0) zcount ,COALESCE(zccount,0) zccount ,COALESCE(yhcount,0) yhcount ,COALESCE(zgcount,0) zgcount ,case when value >100 then 100.00||'%' WHEN value IS NULL THEN 0||'%' else value||'%' end zcl,case when value >100 then 100.00 WHEN value IS NULL THEN 0 else value end value    from   (select sum(whccount) wzccount,sum(zcount) zcount,sum(hccount) zccount,sum(yhcount) yhcount,sum(zgcount) zgcount,round(sum(hccount)*1.00*100/sum(zcount),2)||'%' zcl, round(sum(hccount)*1.00*100/sum(zcount),2) value from hcqkxztext(@startime,@endtime::TIMESTAMP,@yhly,@tablename) where towndm like @code)a
 ";
                }
                if (RegionCode.Length == 12)
                {
                    sql = @" select COALESCE(wzccount,0) wzccount,COALESCE(zcount,0) zcount ,COALESCE(zccount,0) zccount ,COALESCE(yhcount,0) yhcount ,COALESCE(zgcount,0) zgcount ,case when value >100 then 100.00||'%' WHEN value IS NULL THEN 0||'%' else value||'%' end zcl,case when value >100 then 100.00 WHEN value IS NULL THEN 0 else value end value   from   (select sum(whccount) wzccount,sum(zcount) zcount,sum(hccount) zccount,sum(yhcount) yhcount,sum(zgcount) zgcount,round(sum(hccount)*1.00*100/sum(zcount),2)||'%' zcl, round(sum(hccount)*1.00*100/sum(zcount),2) value from hcqkcjtext(@startime,@endtime::TIMESTAMP,@yhly,@tablename) where villagedm like @code)a
   ";
                }
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new {@tablename=table_name, @code = "%" + RegionCode + "%", @startime = param.date[0], @endtime = formattedDate, @yhly= param.roletype==0?user.RoleType:param.roletype }).FirstOrDefault();
                var res = new RespRet<object>(true, "Ok", data);
                WriteSystemLog("houseshc/zctj", res, null);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<object>(false, ex.ToString());
                WriteSystemLogError("houseshc/zctj", res, null);
                res.Msg = "系统异常";
                return res;
            }
        }

        /// <summary>
        /// 市级自查饼图
        /// </summary>
        /// <returns></returns>

        [HttpPost("houseshc/zcpie")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<object> ExcutePie([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                string sql = "  ";
                if (param == null || param.date == null || param.date.Count != 2)
                {
                    return new RespRet<object>(false, "请传递时间");
                }
                var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
                var a = JsonRegion(user.District);
                JObject obj = JObject.Parse(user.District);
                string RegionCode = (string)obj["RegionCode"];
                RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0, RegionCode.Length - 3) : RegionCode;


                if (param.date[1] > DateTime.Now)
                {
                    param.date[1] = DateTime.Today;
                }
                param.date[1] = param.date[1].AddHours(23);
                param.date[1] = param.date[1].AddSeconds(59);
                param.date[1] = param.date[1].AddMinutes(59);
                // 假设 param.date[1] 已经被设置为 2025-06-14 23:59:59
                string formattedDate = param.date[1].ToString("yyyy-MM-dd HH:mm:ss");
                string table_name = "zm_building_fwzt_" + param.date[1].Year;

                if (RegionCode.Length == 3 || RegionCode.Length == 6)
                {
                    sql = @" select case when value is null then 0.00 when value>100 then 100.00 else value end value,name from (select round(sum(hccount)*1.00*100/sum(zcount),2) value,'已核查' name from hcqkgqtext(@startime,@endtime::TIMESTAMP,@yhly,@tablename)a) a  ";
                }
                //if (RegionCode.Length == 6)
                //{
                //    sql = @" select case when value is null then 0.00 when value>100 then 100.00 else value end value,name from (select round(sum(jccount)*1.00*100/sum(zcount),2) value,'已检查' name from jcqkgqtext(@startime,@endtime) where xzqdm like @code union all select round(sum(wjccount)*1.00*100/case when sum(zcount)=0 then null else sum(zcount) end ,2) value,'未检查' name  from jcqkgqtext(@startime,@endtime) where xzqdm like @code )a  ";
                //}
                if (RegionCode.Length == 9)
                {
                    sql = @" select case when value is null then 0.00 when value>100 then 100.00 else value end value,name from (select round(sum(hccount)*1.00*100/sum(zcount),2) value,'已核查' name from hcqkxztext(@startime,@endtime::TIMESTAMP,@yhly,@tablename)a where towndm=@code) a  ";
                }
                if (RegionCode.Length == 12)
                {
                    sql = @" select case when value is null then 0.00 when value>100 then 100.00 else value end value,name from (select round(sum(hccount)*1.00*100/sum(zcount),2) value,'已核查' name from hcqkcjtext(@startime,@endtime::TIMESTAMP,@yhly,@tablename)a where villagedm=@code ) a  ";
                }

                var res_data = new List<PieDto>();
                var data = _dbContext.Database.GetDbConnection().Query<PieDto>(sql, new {@tablename=table_name, @code =RegionCode, @startime = param.date[0], @endtime = formattedDate, @yhly = param.roletype == 0 ? user.RoleType : param.roletype }).FirstOrDefault();
                res_data.Add(data);
                res_data.Add(new PieDto { name = "未达标", value = 100 - data.value });

                var res = new RespRet<object>(true, "Ok", res_data);
                WriteSystemLog("houseshc/zcpie", res, null);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<object>(false, ex.ToString());
                WriteSystemLogError("houseshc/zcpie", res, param);
                res.Msg = "系统异常";
                return res;
            }
        }                 


        /// <summary>
        /// 已自查为自查饼状图二级页面，按率倒序排
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("houseshc/zclbar")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<object> Zclbar([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (param == null || param.date == null || param.date.Count != 2)
                {
                    return new RespRet<object>(false, "请传递时间");
                }
                string sql = " select a.xzqdm,a.xzq,COALESCE(zcount,0) zcount,COALESCE(hccount,0)  hccount, case when hcl is null then '0%' else hcl end hcl ,COALESCE(whccount,0) whccount,COALESCE(yhcount,0) yhcount,COALESCE(zgcount,0) zgcount,COALESCE(syyhcount,0)  syyhcount, case when  value is null then '0' else value end value  From  (select xzqdm,xzq from zm_buildings GROUP BY xzqdm,xzq)a left join ( select *,replace(hcl, '%','') value from hcqkgqtext(@startime,@endtime,@yhly) ORDER BY replace(hcl, '%','')::numeric   desc,xzqdm) b  on a.xzqdm =b.xzqdm  ORDER BY value ::numeric desc,xzqdm\r\n ";

                var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
                var a = JsonRegion(user.District);
                JObject obj = JObject.Parse(user.District);
                string RegionCode = (string)obj["RegionCode"];
                RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0, RegionCode.Length - 3) : RegionCode;


                if (param.date[1] > DateTime.Now)
                {
                    param.date[1] = DateTime.Today;
                }
                param.date[1] = param.date[1].AddHours(23);
                param.date[1] = param.date[1].AddSeconds(59);
                param.date[1] = param.date[1].AddMinutes(59);
                // 假设 param.date[1] 已经被设置为 2025-06-14 23:59:59
                string formattedDate = param.date[1].ToString("yyyy-MM-dd HH:mm:ss");
                string table_name = "zm_building_fwzt_" + param.date[1].Year;


                bool isxz = true;
                if (string.IsNullOrWhiteSpace(param.code))
                {
                    isxz = false;
                    param.code = RegionCode;
                }
                param.code = param.code.Substring(param.code.Length - 3) == "000" ? param.code.Substring(0, param.code.Length - 3) : param.code;

                if (param.code.Length == 3 || param.code.Length == 6)
                {
                    sql = @"  select a.xzqdm,a.xzq,COALESCE(zcount,0) zcount,COALESCE(hccount,0)  zccount, case when hcl is null then '0%' else hcl end zcl ,COALESCE(whccount,0) wzccount,COALESCE(yhcount,0) yhcount,COALESCE(zgcount,0) zgcount,COALESCE(syyhcount,0)  syyhcount, case when  value is null then '0' else value end value  From  (select xzqdm,xzq from zm_buildings GROUP BY xzqdm,xzq)a left join ( select *,replace(hcl, '%','') value from hcqkgqtext(@startime,@endtime::TIMESTAMP,@yhly,@tablename) ORDER BY replace(hcl, '%','')::numeric   desc,xzqdm) b  on a.xzqdm =b.xzqdm  ORDER BY value ::numeric desc,xzqdm
 "; }
                if (isxz)
                {
                    if (param.code.Length == 6)
                    {
                        sql = @"  
  select a.towndm,a.town town,COALESCE(zcount,0) zcount,COALESCE(hccount,0)  zccount, case when hcl is null then '0%' else hcl end zcl ,COALESCE(whccount,0) wzccount,COALESCE(yhcount,0) yhcount,COALESCE(zgcount,0) zgcount,COALESCE(syyhcount,0)  syyhcount, case when  value is null then '0' else value end value  From  (select towndm,max(town) town from zm_buildings GROUP BY towndm)a left join ( select *,replace(hcl, '%','') value from hcqkxztext(@startime,@endtime::TIMESTAMP,@yhly,@tablename)) b  on a.towndm =b.towndm
 where a.towndm like @code  ORDER BY value::numeric desc,towndm

   ";
                    }
                }

                if (param.code.Length == 9 || (param.code.Length == 12))
                {
                    sql = @"  select a.villagedm,a.village,COALESCE(zcount,0) zcount,COALESCE(hccount,0)  zccount, case when hcl is null then '0%' else hcl end zcl ,COALESCE(whccount,0) wzccount,COALESCE(yhcount,0) yhcount,COALESCE(zgcount,0) zgcount,COALESCE(syyhcount,0)  syyhcount, case when  value is null then '0' else value end value  From  (select villagedm,max(village) village from zm_buildings GROUP BY villagedm)a left join ( select *,replace(hcl, '%','') value from hcqkcjtext(@startime,@endtime::TIMESTAMP,@yhly,@tablename)) b  on a.villagedm =b.villagedm
 where a.villagedm like @code  ORDER BY value::numeric desc,villagedm ";
                }
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new {@tablename=table_name, @code = "%" + param.code + "%", @startime = param.date[0], @endtime = formattedDate, @yhly = param.roletype == 0 ? user.RoleType : param.roletype }).ToList();
                //Dictionary<string, object> dictionary = new Dictionary<string, object>();
                //if (data != null)
                //{
                //    var properties = data as IDictionary<string, object>;
                //    foreach (var kvp in properties)
                //    {
                //        var stringlist = kvp.Value.ToString().Trim('[', ']').Split(", ");
                //        var list = new List<string>();
                //        foreach (var item in stringlist)
                //        {
                //            list.Add(item);
                //        }
                //        dictionary.Add(kvp.Key, list);
                //    }
                //    var r = new RespRet<object>(true, "Ok", dictionary);
                //    WriteSystemLog("housesjc/zclbar", r, null);
                //    return r;
                //}
                var res = new RespRet<object>(true, "Ok", data);
                WriteSystemLog("houseshc/zclbar", res, null);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<object>(false, ex.ToString());
                WriteSystemLogError("houseshc/zclbar", res, null);
                res.Msg = "系统异常";
                return res;
            }
        }





          /// <summary>
        /// 已自查为自查饼状图二级页面，按率倒序排 ,三层及以上
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("houseshc/zclscbar")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<object> zclscbar([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (param == null || param.date == null || param.date.Count != 2)
                {
                    return new RespRet<object>(false, "请传递时间");
                }
                string sql = " select a.xzqdm,a.xzq,COALESCE(zcount,0) zcount,COALESCE(hccount,0)  hccount, case when hcl is null then '0%' else hcl end hcl ,COALESCE(whccount,0) whccount,COALESCE(yhcount,0) yhcount,COALESCE(zgcount,0) zgcount,COALESCE(syyhcount,0)  syyhcount, case when  value is null then '0' else value end value  From  (select xzqdm,xzq from zm_buildings GROUP BY xzqdm,xzq)a left join ( select *,replace(hcl, '%','') value from hcqkgqtext(@startime,@endtime,@yhly) ORDER BY replace(hcl, '%','')::numeric   desc,xzqdm) b  on a.xzqdm =b.xzqdm  ORDER BY value desc,xzqdm\r\n ";

                var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
                var a = JsonRegion(user.District);
                JObject obj = JObject.Parse(user.District);
                string RegionCode = (string)obj["RegionCode"];
                RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0, RegionCode.Length - 3) : RegionCode;
                bool isxz = true;
                if (string.IsNullOrWhiteSpace(param.code))
                {
                    isxz = false;
                    param.code = RegionCode;
                }
                param.code = param.code.Substring(param.code.Length - 3) == "000" ? param.code.Substring(0, param.code.Length - 3) : param.code;

                if (param.date[1] > DateTime.Now)
                {
                    param.date[1] = DateTime.Today;
                }
                param.date[1] = param.date[1].AddHours(23);
                param.date[1] = param.date[1].AddSeconds(59);
                param.date[1] = param.date[1].AddMinutes(59);
                // 假设 param.date[1] 已经被设置为 2025-06-14 23:59:59
                string formattedDate = param.date[1].ToString("yyyy-MM-dd HH:mm:ss");
                string table_name = "zm_building_fwzt_" + param.date[1].Year;
                string floor_table= "zm_building_floor_"+ param.date[1].Year;

                if (param.code.Length == 3 || param.code.Length == 6)
                {
                    sql = @"  select a.xzqdm,a.xzq,COALESCE(zcount,0) zcount,COALESCE(hccount,0)  zccount, case when hcl is null then '0%' else hcl end zcl ,COALESCE(whccount,0) wzccount,COALESCE(yhcount,0) yhcount,COALESCE(zgcount,0) zgcount,COALESCE(syyhcount,0)  syyhcount, case when  value is null then '0' else value end value  From  (select xzqdm,xzq from zm_buildings GROUP BY xzqdm,xzq)a left join ( select *,replace(hcl, '%','') value from hcqkgqtext_sc(@startime,@endtime::TIMESTAMP,@yhly,@tablename,@floortable) t1 ORDER BY replace(hcl, '%','')::numeric   desc,xzqdm) b  on a.xzqdm =b.xzqdm  ORDER BY value desc,xzqdm
 "; }
                if (isxz)
                {
                    if (param.code.Length == 6)
                    {
                        sql = @"  
  select a.towndm,a.town town,COALESCE(zcount,0) zcount,COALESCE(hccount,0)  zccount, case when hcl is null then '0%' else hcl end zcl ,COALESCE(whccount,0) wzccount,COALESCE(yhcount,0) yhcount,COALESCE(zgcount,0) zgcount,COALESCE(syyhcount,0)  syyhcount, case when  value is null then '0' else value end value  From  (select towndm,max(town) town from zm_buildings GROUP BY towndm)a left join ( select *,replace(hcl, '%','') value from hcqkxztext_sc(@startime,@endtime::TIMESTAMP,@yhly,@tablename,@floortable) ORDER BY replace(hcl, '%','')::numeric   desc,towndm) b  on a.towndm =b.towndm
 where a.towndm like @code  ORDER BY value desc,towndm

   ";
                    }
                }

                if (param.code.Length == 9 || (param.code.Length == 12))
                {
                    sql = @"  select a.villagedm,a.village,COALESCE(zcount,0) zcount,COALESCE(hccount,0)  zccount, case when hcl is null then '0%' else hcl end zcl ,COALESCE(whccount,0) wzccount,COALESCE(yhcount,0) yhcount,COALESCE(zgcount,0) zgcount,COALESCE(syyhcount,0)  syyhcount, case when  value is null then '0' else value end value  From  (select villagedm,max(village) village from zm_buildings GROUP BY villagedm)a left join ( select *,replace(hcl, '%','') value from hcqkcjtext_sc(@startime,@endtime::TIMESTAMP,@yhly,@tablename,@floortable) ORDER BY replace(hcl, '%','')::numeric   desc,villagedm) b  on a.villagedm =b.villagedm
 where a.villagedm like @code  ORDER BY value desc,villagedm ";
                }
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new {@tablename=table_name,@floortable=floor_table, @code = "%" + param.code + "%", @startime = param.date[0], @endtime =formattedDate, @yhly = param.roletype == 0 ? user.RoleType : param.roletype }).ToList();
                //Dictionary<string, object> dictionary = new Dictionary<string, object>();
                //if (data != null)
                //{
                //    var properties = data as IDictionary<string, object>;
                //    foreach (var kvp in properties)
                //    {
                //        var stringlist = kvp.Value.ToString().Trim('[', ']').Split(", ");
                //        var list = new List<string>();
                //        foreach (var item in stringlist)
                //        {
                //            list.Add(item);
                //        }
                //        dictionary.Add(kvp.Key, list);
                //    }
                //    var r = new RespRet<object>(true, "Ok", dictionary);
                //    WriteSystemLog("housesjc/zclbar", r, null);
                //    return r;
                //}
                var res = new RespRet<object>(true, "Ok", data);
                WriteSystemLog("houseshc/zclbar", res, null);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<object>(false, ex.ToString());
                WriteSystemLogError("houseshc/zclbar", res, null);
                res.Msg = "系统异常";
                return res;
            }
        }







    }
}
